Percent of group total

[ghetus]

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:

[tringuyenminh92 · Accepted 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.

• Create % for the smallest level (items)

% level 3 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Item])  ) )

• The Second level

% level 2 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Category])  ) )

• And the first level (markets)

% 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

• Create measure for Level 2:

Level 2 = if([% level 3]=1 , [% level 2],[% level 3])

• Finally, create measure for level 1 with name % group:

% 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

[Anonymous]

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!

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous]

Hello,

Here are the reults I want. 

I appreciate any help you can give.

- Stacey

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Hello,

The file can be found here:  https://1drv.ms/u/s!AjomjdFc4LMegbsBv_xr6JTOJBcTmQ

Thank you,

-Stacey

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[tyw214]

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!

[Anonymous]

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

[Ashish_Mathur]

Hi,

There is no file that link.

[Anonymous]

Hello,

My apologies.  Please try this link:  https://1drv.ms/u/s!AjomjdFc4LMegbsGbGiMocCsHwJR_w

Thank you,

- Stacey

[Ashish_Mathur]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous]

Hello,

I'm afraid that is identical to the last measure we tried and it is returning the same results.  

- Stacey