Forum Discussion
Summarize all values for subtotal in Matrix
I have a matrix I am using to calculate the most up-to-date sales numbers from my database. Each day the new total is updated to the database, so I wrote a measure to grab the most up-to-date sales number for each customer. The problem comes when I want to show the total of each division (in this case, division "I" should have 1841 as its subtotal, not the minimum value (in this case, 478).
How do I specify to sum the distinct values for each division? I would even be happy with turning off subtotals, and adding an additional column with to show that data. Preferably we could adjust how power BI calculates subtotals.
Let me know if there is a way to do this! Thanks for your Time!
Marco C.
Hi Anonymous ,
Here are the steps you can follow：
1. Create measure.
Measure = IF( NOT(ISINSCOPE('Table'[Division])),SUMX(ALL('Table'),[GranSalesToday]), IF( ISINSCOPE('Table'[Group]), SUMX(FILTER(ALL('Table'),'Table'[Division]=MAX('Table'[Division])&&'Table'[Group]=MAX('Table'[Group])),[GranSalesToday]) , IF( NOT(ISINSCOPE('Table'[Group])), SUMX(FILTER(ALL('Table'),'Table'[Division]=MAX('Table'[Division])),[GranSalesToday]) ,0) ))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Also, this specifically may help in your case: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community
Hi Anonymous ,
Here are the steps you can follow：
1. Create measure.
Measure = IF( NOT(ISINSCOPE('Table'[Division])),SUMX(ALL('Table'),[GranSalesToday]), IF( ISINSCOPE('Table'[Group]), SUMX(FILTER(ALL('Table'),'Table'[Division]=MAX('Table'[Division])&&'Table'[Group]=MAX('Table'[Group])),[GranSalesToday]) , IF( NOT(ISINSCOPE('Table'[Group])), SUMX(FILTER(ALL('Table'),'Table'[Division]=MAX('Table'[Division])),[GranSalesToday]) ,0) ))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Sorry for the delay, but thanks for the response! I tried to modify that measure to fit my table, and I must have made a mistake. My Results are just a bit off :D. The Division is the letters "A, FD, H..." and the drilldown is Salesmand Numbers. My Table that holds all of this data is 'SA511P'. Let me know if you need any other info!
