Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Community Folks,
The matrix visualization in my report is summing up the measures by default on its Total. But, I wanted to have a difference (subtract) value on the Total. Please, ref the below screenshots and I have also attached .xlsx and .pbix files with this post for your kind ref.
We are creating a group in Matrix out of a single field as shown in the above screenshot.
We need to tally the Amount of Total assets and Amount of Liabilities & Equity to Zero.
Total = Sum of Total Assets - Sum of Total Liabilities & Equity
Total = 120 - 120 = 0
But, what we are getting in Power BI matrix is the summed value. Total = 240 as depicted in the above screenshot,
Is there any way to get the subtracted value,
Total = 120 - 120 Total = 0.
Your timely help is much appreciated.
Many thanks,
Praveen.
Solved! Go to Solution.
Hi @Anonymous
You may try to create a measure to change the total value. Such as:
Measure = IF ( ISFILTERED ( 'Single Column Data'[Major group] ), SUMX ( 'Single Column Data', 'Single Column Data'[Amount] ), SUMX ( FILTER ( 'Single Column Data', 'Single Column Data'[Major group] = "Assets" ), 'Single Column Data'[Amount] ) - SUMX ( FILTER ( 'Single Column Data', 'Single Column Data'[Major group] = "Liabilities & Equity" ), 'Single Column Data'[Amount] ) )
Regards,
Cherie
Hi Cherie
How do we apply time dimesion to the below your query. As we have to calculate Prior Month,Prior Qtr, Prior Year etc.
Measure = IF ( ISFILTERED ( 'Single Column Data'[Major group] ), SUMX ( 'Single Column Data', 'Single Column Data'[Amount] ), SUMX ( FILTER ( 'Single Column Data', 'Single Column Data'[Major group] = "Assets" ), 'Single Column Data'[Amount] ) - SUMX ( FILTER ( 'Single Column Data', 'Single Column Data'[Major group] = "Liabilities & Equity" ), 'Single Column Data'[Amount] ) )
Thanks
Smita
You wouldn't do that in that measure. Instead you can reference it with time intelligence functions (if you have a separate Calendar-Table for this). See this article for more details for example: https://exceleratorbi.com.au/dax-time-intelligence-beginners/
PriorMonth = CALCULATE( [Measure], DATESADD(‘Calendar'[Date], -1, MONTH))
PriorYear = CALCULATE( [Measure], DATESADD(‘Calendar'[Date], -1, Year))
...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Anonymous
You may try to create a measure to change the total value. Such as:
Measure = IF ( ISFILTERED ( 'Single Column Data'[Major group] ), SUMX ( 'Single Column Data', 'Single Column Data'[Amount] ), SUMX ( FILTER ( 'Single Column Data', 'Single Column Data'[Major group] = "Assets" ), 'Single Column Data'[Amount] ) - SUMX ( FILTER ( 'Single Column Data', 'Single Column Data'[Major group] = "Liabilities & Equity" ), 'Single Column Data'[Amount] ) )
Regards,
Cherie
Hello Cherrie,
That's marvelous!!! Thank you so much for your help. This really works for me.
Many thanks,
Praveen.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |