cancel
Showing results for
Did you mean:
Helper I

## how to divide subcategory sum by category total

Hi Guys,

Anyone know how I can create measures that give the percentage figures as shown in the pivot table below? The category totals are 125 and 275 and whenever i try to create a percentage total of each subcategory ie Plymouth or SHWD, it keeps dividing the subcategory numerator total by the respective subcategory denominator total (15, 110, 210 and 65). I need a measure that can divide the subcategory numerator total by the respective category denominator total instead (125 or 275) as shown on the pivot table. I have also included the pbix file and when you look at it, it shows the percentage figures for each subcategory when its numerator total is divided by the subcategory denominator total and this is not what i want to see. I need it to be abe to calculate the percentages just as it is on the pivot table.

Any help will be highly appreciated.

pivot table

2 ACCEPTED SOLUTIONS
Solution Specialist

Hi try this

Measure =

var cat =SELECTEDVALUE (category Column)

return

if(ISINSCOPE(sub-category Column),
sum of numberator / CALCULATE(sum of denominator, REMOVEFILTER(sub-category column), category Column = cat),
sum of numbertor/sum of denominator)

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Thank you @Ashish_Mathur

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Specialist
Solution Specialist

Hi try this

Measure =

var cat =SELECTEDVALUE (category Column)

return

if(ISINSCOPE(sub-category Column),
sum of numberator / CALCULATE(sum of denominator, REMOVEFILTER(sub-category column), category Column = cat),
sum of numbertor/sum of denominator)

Helper I

Thank you @HoangHugo

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors