- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Percent of group total

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

ghetus

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2017
06:05 AM

**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.

1 ACCEPTED SOLUTION

Accepted Solutions

tringuyenminh92

New Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2017
09:11 AM

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

30 REPLIES 30

ImkeF

Super User

Re: Percent of group total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2017
08:03 AM

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

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

tringuyenminh92

New Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-06-2017
09:11 AM

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

ddoss

Frequent Visitor

Re: Percent of group total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2018
06:02 PM

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

Ashish_Mathur

Super User

Re: Percent of group total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2018
07:40 PM

Hi,

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

ddoss

Frequent Visitor

Re: Percent of group total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2018
12:11 PM

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 Order | MO Status | Outsourced | Costs Consumed Labor | Cost Put into WIP Labor | Change Date | Total Costs Consumed | Total Cost Put into WIP |

1604629 | Closed | Yes | 1,272.54000 | 1,601.92000 | 1/18/2017 | 3,697.38000 | 4,467.02000 |

1604969 | Closed | Yes | 1,747.83000 | 1,662.19000 | 1/18/2017 | 6,207.81000 | 6,032.54800 |

1605069 | Closed | Yes | 1,533.40000 | 1,344.00000 | 1/18/2017 | 3,932.61000 | 3,556.84188 |

1605070 | Closed | Yes | 1,385.80000 | 1,232.86000 | 1/18/2017 | 3,554.07000 | 3,276.73688 |

1605240 | Closed | Yes | 1,108.71000 | 1,585.83000 | 1/18/2017 | 0.00000 | 4,120.27800 |

1605240 | Closed | Yes | 1,108.71000 | 1,585.83000 | 1/18/2017 | 3,099.15000 | 4,120.27800 |

Any help would be greatly appreciated.

Dinora

Ashish_Mathur

Super User

Re: Percent of group total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2018
03:42 PM

Hi,

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

ddoss

Frequent Visitor

Re: Percent of group total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2018
06:42 PM

Ashish,

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

Appreciate your kidness very much.

Dinora

Highlighted
##

Ashish_Mathur

Super User

Re: Percent of group total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2018
07:18 PM

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

StaceyMcCain

Regular Visitor

Re: Percent of group total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-27-2018
12:24 PM

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.