Skip to main content
cancel
Showing results for 
Search instead 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

Reply
AP23
Advocate I
Advocate I

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)

AP23_0-1696863928141.png

How can I get the Total Row to display the correct value please?

1 ACCEPTED SOLUTION

@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


Follow on LinkedIn
@ 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...

View solution in original post

4 REPLIES 4
HotChilli
Super User
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.

@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


Follow on LinkedIn
@ 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...
Greg_Deckler
Super User
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

 


Follow on LinkedIn
@ 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...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors