topic Re: Total % in the Matrix must be a measure to the table in DAX Commands and Tips

Total % in the Matrix must be a measure to the table

onevagi — Thu, 08 Aug 2024 16:09:55 GMT

Hi,

I have a DAX measure which is added to the values tab for the matrix. The Matrix includes Department like Home, Motor ; then Area which includes A, B, C (as example), and inlcudes 3 groups: Planned, Actual, and Difference. There are numbers are planned, actual na and Difference is calculated by Planned - Actual / 7.5. Now at the end it inlcudes Total with 2 other rows as Operational and Planning which are percentages. Total % is difference of Actual - Planning. I need this to be a measure in some other table. How to do it.

Re: Total % in the Matrix must be a measure to the table

Greg_Deckler — Thu, 08 Aug 2024 16:12:47 GMT

onevagi Yours is a bit different but the same technique should apply here, you just need to identify the total row and in that case return your other measure.

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/n4TYhF2ARe8

Re: Total % in the Matrix must be a measure to the table

Anonymous — Fri, 16 Aug 2024 07:04:40 GMT

Hi onevagi ,

I create a table as you mentioned.

Then I create a calculated column and here is the DAX code.

Total % = VAR _PlannedValue = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Group] = "Planned" ) VAR _ActualValue = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Group] = "Actual" ) RETURN ( _PlannedValue - _ActualValue ) / 7.5

Finally you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.