Formula to Return SUM similar to a SQL IN Statement in a GRAND TOTAL

[jplefka]

Scenario:

My organization has ACCOUNT that have multiple balances associated with them, and each account has a TYPE

These accounts tie into a PORTFOLIO

We have a Pivot that is to return all PORTFOLIOS and the SUMs of these ACCOUNTS.  But we would like to filter to return PORTFOLIOS that have ACCOUNTS that are a particular TYPE, however in the event there is one ACCOUNT of this TYPE, then all ACCOUNTS in this PORTFOLIO should be returned as well

Currently the pivot is able to do this to calculate a RATIO using these SUMS, however the grand total row does not work, because the ALL function that we use to get ALL Types grabs all ACCOUNT amounts regardless of if they are in the right PORTFOLIO.  Thus, the grand total is not correct of the ratio, even those the grand total for the amounts is correct that go into the ratio.

How can we design a formula to accomplish this ask?

[Helena_qbeeq]

Good morning @jplefka ,

This is a really interesting scenario.

If I understood well your situation, you are able to show the correct listing of portfolios containing at least one account for a specific type, and the results for each portfolio show the correct value for all corresponding accounts.

However the grand total doesn't show the correct value, since it shows the total amount for all accounts in all portfolios.

You can change that behaviour in the value where you applied the ALL function, by changing the Totals Calculation to sum (or any other available function). It should then return the correct value, summing the results for all portfolios that have ate least one account of specific type.

Please let me know if this helps 🙂

Always here to help,
Helena from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner
www.qbeeq.pl

[juanpmo]

Hi!

What if I'm trying to refer to that calculation with the Totals Calculation to sum into another column?

As I'm trying but it doesn't pull the "Totals Calculation to sum" calculation.