cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: Percent of group total

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
Highlighted
Super User III
Super User III

Re: Percent of group total

You can create a hierarchical measure like described here: http://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Memorable Member
Memorable Member

Re: Percent of group total

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

Highlighted
Frequent Visitor

Re: Percent of group total

Hello Tri,

 

I am creating my first Power BI dashboard and I am having trouble with getting a percent formula working. We have data for manufacturing orders for several years, and we need to create an Labor Efficiency rate. The basic calculation is Cost put into WIP Labor/Cost Consumed Labor. I am using the this formula:

 

Efficiency Percent = DIVIDE(SlbMoStatus[Cost Put into WIP Labor],SlbMoStatus[Costs Consumed Labor])

 

I have set filters to select only year 2017 and for closed orders but my Efficiency rate results are way off. 

 

What am I doing wrong? 

 

Best Regards from USA,

 

Dinora

 

 

 

Highlighted
Super User IV
Super User IV

Re: Percent of group total

Hi,

 

There is no mistake in your formula.  Show some data and your expected result.


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

Re: Percent of group total

Sum of EficiencySum of Eficiency

 

That is the result for my formual. The correct total for January 2017 for only closed manufacturing orders should be 104%

 

I am attaching some sample date for you.

Manufacturing OrderMO StatusOutsourcedCosts Consumed LaborCost Put into WIP LaborChange DateTotal Costs ConsumedTotal Cost Put into WIP
1604629ClosedYes1,272.540001,601.920001/18/20173,697.380004,467.02000
1604969ClosedYes1,747.830001,662.190001/18/20176,207.810006,032.54800
1605069ClosedYes1,533.400001,344.000001/18/20173,932.610003,556.84188
1605070ClosedYes1,385.800001,232.860001/18/20173,554.070003,276.73688
1605240ClosedYes1,108.710001,585.830001/18/20170.000004,120.27800
1605240ClosedYes1,108.710001,585.830001/18/20173,099.150004,120.27800

 

Any help would be greatly appreciated.

 

Dinora

Highlighted
Super User IV
Super User IV

Re: Percent of group total

Hi,

 

You have created Efficiency Percent as a calculated column formula.  You should delete it from there and create it as a Measure.


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

Re: Percent of group total

Ashish,

 

A million times 100, thanks for your reply. It works now. 

 

Appreciate your kidness very much.

 

Dinora

Super User IV
Super User IV

Re: Percent of group total

You are most welcome.  Please mark my reply as Answer.


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

Re: Percent of group total

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.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors