Formula to Return SUM similar to a SQL IN Statement in a GRAND TOTAL
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?