cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
wenners68 Helper II
Helper II

Calculate percentage on subgroup

Hi,

I want to calculate the percentage of each category under each department. 

The total for the Asia Pacific department is £449.70, but what is the percentage for each category? 

So for Asia Pacific: Transport would be around 54.4%, Subsistence 44.95% and Phone 0.6%

Any help appreciated 🙂

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Calculate percentage on subgroup

@wenners68 well then in original measure, change table reference to your department table and it will work






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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: Calculate percentage on subgroup

@wenners68 here is the measure, change table and column name as per your model

 

% = DIVIDE(
 SUM( 'Table'[Amount] ), 
CALCULATE( SUM( 'Table'[Amount] ),
 ALLEXCEPT( 'Table', 'Table'[Department] ) )
 )





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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





wenners68 Helper II
Helper II

Re: Calculate percentage on subgroup

Hi,

 

That gives you a percentage of 100% for each item...

 

Department is in one table and category and amount is in another table.

Super User IV
Super User IV

Re: Calculate percentage on subgroup

@wenners68 I guess you have relationship between department and other table? You never mentioned this in your original post. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





wenners68 Helper II
Helper II

Re: Calculate percentage on subgroup

Sorry, my bad.

 

Yes, there's a relationship between department and category.

Super User IV
Super User IV

Re: Calculate percentage on subgroup

@wenners68 well then in original measure, change table reference to your department table and it will work






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

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

wenners68 Helper II
Helper II

Re: Calculate percentage on subgroup

Thanks, that works 🙂

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors