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
Hello,
I want create report in which will be sum of sales and percent from sum on group. I know in Power BI exists Quick measures, but they calculate percent from grand total, i need calculate percent from the above group.
Below is an example:
Solved! Go to Solution.
Hi @ghetus,
This is first time I'm facing with % total of multiple levels, so I separate it into 3 measures and use a trick with If condition to showing 3 measures for 3 levels.
% level 3 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Item]) ) )
% level 2 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Category]) ) )
% level 1 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Market]) ) )
As my observation, when showng 1 level in matrix, the parent will be 100% or 1, so I will use IF condition to replace this value to its percentage in the previous level
Level 2 = if([% level 3]=1 , [% level 2],[% level 3])
% group = if([Level 2]= 1,[% level 1] ,[Level 2])
For more details, please kindly check my sample file and data for your case.
It's late in Vietnam so i'm going to bed now, hope you take a look with some testing to ensure this is correct for all cases.
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
Hi @ghetus,
This is first time I'm facing with % total of multiple levels, so I separate it into 3 measures and use a trick with If condition to showing 3 measures for 3 levels.
% level 3 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Item]) ) )
% level 2 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Category]) ) )
% level 1 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Market]) ) )
As my observation, when showng 1 level in matrix, the parent will be 100% or 1, so I will use IF condition to replace this value to its percentage in the previous level
Level 2 = if([% level 3]=1 , [% level 2],[% level 3])
% group = if([Level 2]= 1,[% level 1] ,[Level 2])
For more details, please kindly check my sample file and data for your case.
It's late in Vietnam so i'm going to bed now, hope you take a look with some testing to ensure this is correct for all cases.
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
Hi Tringuyenminh92,
Your solution is perfect, but I have little problem.
I tried to modify in different ways your formula before ask you but I couldn't achieve a result.
I want order the rows based in other table with the same rows but ordered by other column.
When I modify the ALLSELECTED like below the results is always 100%
% level 3 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Item Data'[Item]) ) )
Thanks for your help!
This solution works except for when the lowest subgroup has a single value. Do you have any idea how to make it work in that case? Currently, the percent is of the total of that subgroup calculated over all of the level 2's, rather than that specific level 2.
Hi,
Share some data and show the expected result.
Hello,
Below is a table of sample data:
Following the steps in this post, I created the following measures:
%Activity = DIVIDE(SUM(TableA[Hours]), CALCULATE(SUM(TableA[Hours]), ALLSELECTED(TableA[Activity])))
%Charge = DIVIDE(SUM(TableA[Hours]), CALCULATE(SUM(TableA[Hours]), ALLSELECTED(TableA[Charge])))
%Employee = DIVIDE(SUM(TableA[Hours]), CALCULATE(SUM(TableA[Hours]), ALLSELECTED(TableA[Employee])))
Level1 = IF([%Activity]=1, [%Charge], [%Activity])
MeasureSelect = IF([Level1]=1, [%Employee], [Level1])
The resulting matrix is below:
:
Note that where there is a single value at the lowest level (e.g. under Customer Charge), the % is calculating as the % of that value across all employees, rather than for the single employee. Each one of the lines for Project A should show 100% as it is 100% of the values under Customer Charge. Non-customer Charges - where there are more than one at the lowest value - are calculating correctly.
Do you have any idea why this is happening and how to fix it?
Thank you very much for any help you can provide.
- Stacey
Hi,
Based on the first table that you have shared, please share the exact result you are expecting. I will help you with writing the DAX formulas.
Hello,
Here are the reults I want.
I appreciate any help you can give.
- Stacey
Hi,
Share the link from where i can download your PBI file.
Hello,
The file can be found here: https://1drv.ms/u/s!AjomjdFc4LMegbsBv_xr6JTOJBcTmQ
Thank you,
-Stacey
Hi,
You may download my PBI file from here.
Hope this helps.
Hello, I am facing the same problem where the measures work when there are multiple values for a category, but doesn't work when it's a single value.
Can you show me how you fix that problem in that PBI file? Thanks!
Hello,
Thank you for the pbi file! Curiously, why this works with my sample data, when I created corresponding 'Total hours' and 'Measure' in my actual pbi dataset it did not return the same results. In this case, those with a single value at the lowest level correctly calculated to 100%, yet those with multiple values - which previously calculated correctly - now show erroneous %. I cannot even determine what the % returned is representing. I've uploaded the pbix here: https://onedrive.live.com/?id=1EB3E05CD18D263A%2123936&cid=1EB3E05CD18D263A
Thank you,
- Stacey
Hi,
There is no file that link.
Hello,
My apologies. Please try this link: https://1drv.ms/u/s!AjomjdFc4LMegbsGbGiMocCsHwJR_w
Thank you,
- Stacey
Hi,
I cannot open the file. Here is the error message i received. Also, that is a 21.3 MB file. Please reduce the file down to a couple of MB's. Thank you.
Hello,
I removed all tables but those referenced for this visual and also removed the older data to reduce the file size. Interestingly, after making those changges I see that the measure is now displaying the correct percentages. There must have been an issue with the data causing the erroneous percentages I was seeing previously.
Thank you for all your help,
- Stacey
Hi again,
Looks like I spoke too soon. As I look further down I do find where some are still not calculating properly. In the file found at the following link, please scroll down the visual until you see the name Daniel Robles. You will see that his Non-Segment Time shows 11.11% and 14.81% where it should show 42.86% and 57.14%
https://1drv.ms/u/s!AjomjdFc4LMegbsI9eUJ-veqoPgMUA
Thanks,
- Stacey
Also, if you get the error message, then it means you need to open the Power BI application, go to File > Options > Preview Features and check the box for Composite Models. You then close the app and then reopen Power BI and open the pbix.
Thanks,
-Stacey
Hi,
Try this measure
Hours%Hierarchy = if(HASONEFILTER(vAllTRData[ActivityName]),[Total hours]/CALCULATE([Total hours],ALLSELECTED(vAllTRData[ActivityName])),if(HASONEFILTER(vAllTRData[TimeIsToSegment]),[Total hours]/CALCULATE([Total hours],ALLSELECTED(vAllTRData[TimeIsToSegment])),[Total hours]/CALCULATE([Total hours],ALL(vAllTRData[TeamMemberName]))))
Hope this helps.
Hello,
I'm afraid that is identical to the last measure we tried and it is returning the same results.
- Stacey
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 |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
104 | |
64 | |
60 |