cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## 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?

1 ACCEPTED SOLUTION
Super User

@AP23 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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Super User

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.

Super User

@AP23 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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

@AP23 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``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.