Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
3 Tables - Acct, Shares, Certificates.
Total Deposits = sum(Share Balance) + sum(CertBal)
I want to display only those where "Total Deposits > 500,000" as "uninsured." The problem is that the Total of the "accounts" doesn't match the total of the groups. I have tried "calculate - filter" and various "sumx" but I have the same problem where the Total of the Group doesn't equal the group total. 😞
Solved! Go to Solution.
Hi @kaniggit
Something like this pattern will be close. I have attached a PBIX file for you to have a play with.
Uninsured = IF( ISFILTERED('Table1'[Sub Group]), -- Then -- IF([Total Deposits]>=500,[Total Deposits],0), -- Else -- SUMX( FILTER( ALL(Table1[Group],Table1[Sub Group]), 'Table1'[Group] = MAX('Table1'[Group]) ), IF([Total Deposits]>=500,[Total Deposits],0) ) )
From Phil (above)
Uninsured = SWITCH(TRUE() , -- Handle the Acct lines ISFILTERED('Acct'[Acct]) , IF([Total_Deposits]>500000,[Total_Deposits]-500000,0) , -- Handle the Group sumx( FILTER( ALL(Acct[Group],Acct[Acct]), Acct[Group] = MAX(Acct[Group]) ), IF([Total_Deposits]>=500000,[Total_Deposits]-500000,0) ) )
This worked for me!!
Hi @kaniggit
Something like this pattern will be close. I have attached a PBIX file for you to have a play with.
Uninsured = IF( ISFILTERED('Table1'[Sub Group]), -- Then -- IF([Total Deposits]>=500,[Total Deposits],0), -- Else -- SUMX( FILTER( ALL(Table1[Group],Table1[Sub Group]), 'Table1'[Group] = MAX('Table1'[Group]) ), IF([Total Deposits]>=500,[Total Deposits],0) ) )
I did that, but it still doesn't quite seem to work.? I think this has everything to do with "row context"? I'm still new to all of this.
Here is my formula and the result... The "Group A" total would be correct ($800-$500 = $300), but it is the sub-group (Account) totals that I want to "rollup" to the Group. Maybe I screwed something up by using the "[Total Deposits]-500000"?
Also, the formula for "Total_Deposits" is:
Total_Deposits = calculate(sum(Share[Share Balance]) + sum(Cert[CertBal]))
Should I change that to some SUMX or something with "RELATEDTABLE"?
From Phil (above)
Uninsured = SWITCH(TRUE() , -- Handle the Acct lines ISFILTERED('Acct'[Acct]) , IF([Total_Deposits]>500000,[Total_Deposits]-500000,0) , -- Handle the Group sumx( FILTER( ALL(Acct[Group],Acct[Acct]), Acct[Group] = MAX(Acct[Group]) ), IF([Total_Deposits]>=500000,[Total_Deposits]-500000,0) ) )
This worked for me!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |