Forum Discussion
Matrix table Total Row shows incorrect total value for Percent column
Hello all,
Please could I ask for some assistance.
I have a matrix with a column which is showing a percentage.
I also have a total row but instead of using the same calculation, it is instead summing all the percentages which displays an incorrect figure.
e.g.
Measure FTE % to Reg'd No.s = SUM(FTE[FTE]) / MIN(FTE[data.siteCapacity])
In the table, this is shown as column 'Forecast' / column 'Registered Numbers'.
As you can see from the screen print, the row data is correct but the Total Row shows 677% when it should be Forecast / Registered numbers (352.1 / 597 = 59% to 1dp)
How can I get the Total Row to display the correct value please?
Anonymous I think HotChilli is correct, seems like you need to get the correct total for your capacity. You can do that following this guidance (below). Alternatively perhaps change your MIN to SUM for getting your capacity.
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
4 Replies
352 / 52 as a percentage = 677 so it's all correct but not what you wanted. There's enough documented posts on measure totals out there so you can go off and research those for more info.
The solution is usually to iterate over each line of the table and add the measure calculated for each line. I can't tell from the picture what the right granularity would be.
Is that enough to start you on the right path?
Anonymous I think HotChilli is correct, seems like you need to get the correct total for your capacity. You can do that following this guidance (below). Alternatively perhaps change your MIN to SUM for getting your capacity.
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
Anonymous Usually this is the opposite problem. I would modify the measure to this (below). Then, modify your RETURN statement to be your __FTEs and __Capacity VAR's to confirm the numbers being returned in the Total row. My guess is that the problem will come down to how you are calculating the capacity.
Measure FTE % to Reg'd No.s = VAR __FTEs = SUM(FTE[FTE]) VAR __Capacity = MIN(FTE[data.siteCapacity]) VAR __Result = DIVIDE( __FTEs, __Capacity ) RETURN __Result
