cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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])))

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

16 REPLIES 16
Community Support

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

Resolver I

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

Solution Sage

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.

Resolver I

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

Solution Sage

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')))

Resolver I

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

Solution Sage

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.

Resolver I

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

Solution Sage

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.

Resolver I

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

Solution Sage

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.

Resolver I

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

Solution Sage

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.

Resolver I

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

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Sage

Try below:

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

Let me know if it works fine.

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors