How to get percentage of subtotal

[sujitjena]

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.

[v-yetao1-msft]

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]

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

[mhossain]

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

[sujitjena]

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

[mhossain]

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

[sujitjena]

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

[mhossain]

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

[sujitjena]

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

[mhossain]

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

[sujitjena]

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

[mhossain]

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

[sujitjena]

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

[mhossain]

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

[sujitjena]

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

Hi,

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

[mhossain]

@sujitjena 

Try below:

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

Let me know if it works fine.