how to divide subcategory sum by category total

[Shenee]

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.

https://1drv.ms/u/s!AhEw68UYqcc6gx0mgWUJ4xXXbX7v?e=RtGyph 

pivot table

[HoangHugo · Accepted solution]

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)

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Shenee]

Thank you @Ashish_Mathur 

[Ashish_Mathur]

You are welcome.

[HoangHugo]

See updated file by link below

https://drive.google.com/file/d/15k1M6f5OC-IVJo9uMgoY55gdoNzqR5ew/view?usp=sharing

[HoangHugo · Accepted solution]

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)

[Shenee]

Thank you @HoangHugo 

Your measure worked brilliantly!