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
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
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.