Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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")
I may be missing a minor trick. Any suggestions would be helpful.
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
@mhossain : This works for total Percentage changes and is no longer 100%. below screenshot.
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.
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.
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 : 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!
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!
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.
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
Hi,
Share the link from where i can download your PBI file and show the expected result as well.
Try below:
% from Total Hrs PY = DIVIDE([Prod Hrs],CALCULATE([Prod Hrs],ALL('Mapping')))
Let me know if it works fine.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |