Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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?
Solved! Go to Solution.
@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
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?
Ah! Couldn't see the woods for the trees! Thanks for this, totally understand where I was going wrong now.
@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
User | Count |
---|---|
82 | |
75 | |
70 | |
41 | |
36 |
User | Count |
---|---|
114 | |
56 | |
52 | |
43 | |
42 |