cancel
Showing results for 
Search instead for 
Did you mean: 

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

jplefka
7 - Data Storage
7 - Data Storage

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?

 

 

1 REPLY 1

Helena_qbeeq
8 - Cloud Apps
8 - Cloud Apps

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.

 

Helena_0-1671614450600.pngHelena_1-1671614469692.png

Please let me know if this helps 🙂

 

Always here to help,
Helena from QBeeQ
helena.ferreira@qbeeq.pl
QBeeQ - Gold Implementation and Development Partner
www.qbeeq.pl