cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ghetus
Frequent Visitor

Percent of group total

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:

img-2017-08-06-15-51-26.png

1 ACCEPTED SOLUTION
tringuyenminh92
Memorable Member
Memorable Member

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

 

Screenshot 2017-08-06 23.07.27.png

 

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

View solution in original post

30 REPLIES 30
tringuyenminh92
Memorable Member
Memorable Member

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

 

Screenshot 2017-08-06 23.07.27.png

 

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

View solution in original post

Anonymous
Not applicable

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
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hello,

 

Below is a table of sample data:

 

 Sample data.JPG

 

 

 

 

 

 

 

 

 

 

 

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:

 

:Sample matrix.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hello,

 

Here are the reults I want. 

 

 

Desired Results.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I appreciate any help you can give.

 

- Stacey

 

 

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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
Not applicable

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
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hello,

 

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

 

- Stacey

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors