Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sujitjena
Resolver I
Resolver I

How to get percentage of subtotal

Hi,

I am trying to get percentage of subtotal in a matrix. The simplest way to get this would be to get a "percentage of grand total" and it works. However, in my case i have to calculate a difference of these percenatges from two cilumns and therefore i need to calculate it as a measure. Below is the illustration: (FYI: Mapping table is connected to Productivity Table)

Below formula works at total level but not at a sub category level.

Prod Hrs = CALCULATE(SUM('Productivity Data'[Productivity]),'Mapping'[Category]<>"SAH")

% from Total Hrs PY = DIVIDE([Prod Hrs],CALCULATE([Prod Hrs],ALLEXCEPT('Mapping','Mapping'[Sub Category])))
 

Sample 1.PNG 

I may be missing a minor trick. Any suggestions would be helpful.

 

16 REPLIES 16
v-yetao1-msft
Community Support
Community Support

Hi @sujitjena 

Has your problem been solved so far ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regard

Community Support Team _ Ailsa Tao

sujitjena
Resolver I
Resolver I

@mhossain : This works for total Percentage changes and is no longer 100%. below screenshot.Sample 2.PNG

@sujitjena 

 

If total is not 100%, means you are missing some numbers in the numerator, check the mapping table, and filter if you are applying page/visual level for the mapping table fields.

@mhossain : Yes thats the tricky part. I have filter applied in the measure for numerator:

Prod Hrs = CALCULATE(SUM('Productivity Data'[Productivity]),'Mapping'[Category]<>"SAH")

However, this works when i use a %of grand total and this is what i cant use for now.

@sujitjena 

Ok, so you need to exclude SAH from total (denominator) too. If you can share dummy pbix with same structure, should be quick. Hope below works

 

% from Total Hrs PY = 

DIVIDE([Prod Hrs],

CALCULATE([Prod Hrs], 'Mapping'[Category]<>"SAH", ALL('Mapping')))

 

@mhossain : I have shared the sample Pbix file below. 

https://ln5.sync.com/dl/775f9dff0/55msngu2-7sebpbc4-47g4sf45-zjj6iven

Let me know if you cant access.

@sujitjena 

Please see the screenshot, check the "Sub Category" filter at visual level filter, this is causing the total number issue, if you clear it, showing 100%. Some options are randomely selected, and in measures also some names looks different like "Vacation / PTO". Please clean it properly current dax will work.

mhossain_0-1638796784345.png

 

@mhossain : You are right, the selection in the filter section is taken out for Vacation/PTO & Holiday. But thats the requirement. If i add back those two, the total in the Hrs FY21 column changes. I need to either tweak this in the % column to pick up total and not the selected ones or tweak in the Hrs FY21 column. Let me know if there is a way to acheive this either of the ways. Thanks for your help!

@sujitjena 

Definately there are ways to achieve it, would suggest for now clear the visual level filter and mentioned <> all filters in your measure as you already are mentioning for some categories.

Numerator and denominator both should have these <> filters and in denominator additional all(tablename) filter.

Hope this is clear.

 

If required I will try to share example in your sample pbix if I get time later. Please try above.

@mhossain : I tried and it doesnt work. Please share with an example if possible. Thanks! 

@sujitjena 

See the attached file, filters applied in measures, numbers are coming as expected.

I am not sure about the filters you are applying what is the business objective behind this, so just applied filters as you mentioned in the sample file.

Hope this solves.

@mhossain : Thanks for sharing the example but what i want is a measure and not a "% of grand total". The reason is i want a difference of two columns with "% of grand total" which is not possible with the approach you shared. Is there a way to build a measure instead of getting "% of grand total"? Thanks a lot for your effort and time.

@sujitjena 

I tried as per your screenshot provided in your main question, but now I am little lost on the requirement, please explain and share more details with the screenshot maybe from start, definately will try to help.

 

@mhossain : I have shared the sample file with the exact requirement and the measures i have built, at below link. 

To explain a bit - The two columns with percentages for subcategories are not working, if you can find a way to fix them, it should work. Rest of the columns are fine.

 https://ln5.sync.com/dl/775f9dff0/55msngu2-7sebpbc4-47g4sf45-zjj6iven 

Ashish_Mathur
Super User
Super User

Hi,

Share the link from where i can download your PBI file and show the expected result as well.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mhossain
Solution Sage
Solution Sage

@sujitjena 

 

Try below:

 

% from Total Hrs PY = DIVIDE([Prod Hrs],CALCULATE([Prod Hrs],ALL('Mapping')))

 

Let me know if it works fine.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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