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
tsdick
Resolver I
Resolver I

PBI Matrix Percentage Column Subtotals and Totals Incorrect for Hierarchy

In the following matrix visual the hierarchy margin % column subtotals are summing rather than recalculating for each level in the hierarchy (margin in red should be calc'd as (7,553/42,355=17.8%) not summed from the lines in blue (31+4.2+18=53.2%).  The margin amounts for the lines in blue are correct (5,081/16,392=31%).  I am hoping this can be corrected in some way.  I have already tried disabling summation, rewriting the DAX expression using a calculated column, but not having any luck changing the percentage column subtotals in the matrix for the hierarchy .  Help is appreciated!

 

 

tsdick_0-1650307196916.png

 

1 ACCEPTED SOLUTION
fhill
Resident Rockstar
Resident Rockstar

How are you calculating Margin?  I created a mock-up where I'm getting different results?  Try this Measure for Margin instead?

 

fhill_0-1650310707211.png

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




View solution in original post

6 REPLIES 6
fhill
Resident Rockstar
Resident Rockstar

Replying to move this thread back to the top and keep it alive.  Based on what i'm seeing in your DAX code, you may need to post a sample of you dataset so we can test further...




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




tsdick
Resolver I
Resolver I

Sorry to quick on the reply trigger...  

 

Margin calc'd as follows...

 

tsdick_2-1650312180103.png

Having difficulties w/ SUM...

Thanks for the continued help....

 

fhill
Resident Rockstar
Resident Rockstar

Check out this useful guide about Totals and Power BI Visuals.  It's not uncommon that you need more control over the Matrix 'default' math.  Using 'HasOneValue' in some IF logic helps you choose how you want Detail vs. Summary rows to calculate...  I hope this helps!

 

https://blog.enterprisedna.co/getting-totals-correct-when-using-advanced-logic/




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




I tried HASONEVALUE (using the last field in the hierarchy) and got the same result.  Any suggestions?

 

tsdick_0-1650309766049.png

 

fhill
Resident Rockstar
Resident Rockstar

How are you calculating Margin?  I created a mock-up where I'm getting different results?  Try this Measure for Margin instead?

 

fhill_0-1650310707211.png

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




 

 

tsdick_0-1650310893339.png

 

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