How to Calculate %Proportion of Hierarchy in Matrix

[201TinyTim]

I have a matrix in the style of the below  with a heirarchy of country->product  and measures of "Special" products being for example only products A and B. (currently just a binary flag against products of if special or not and then seperate calculate for that to get Special only column)

Currently it is doing Proportion % per row   (i.e each row uses the underlined value as the denominator and the italics as the numerator) and i have tried multiple ways to make be Proportion of the Hierarchy tier   so it shows the product value proportion % compared to the  country total.  (Each Italics numerator is compared to the same single underlined denominator per country)

Current Version

Hierarchy	Sum All Products	Sum Special Only	Proportion% of Special in All
CountryA	1000	250	25%
->ProductA	100	100	100%
->ProductB	150	150	100%
->ProductC	750
CountryB etc	250	25	10%
Total	1250	275	22%

Desired Version

Hierarchy	Sum All Products	Sum Special Only	Proportion% of Special in All
CountryA	1000	250	25%
->ProductA	100	100	10%
->ProductB	150	150	15%
->ProductC	750
CountryB etc	250	25	10%
Total	1250	275	22%

Any and all help would be greatly appreciated

[Anonymous · Accepted solution]

Hi @201TinyTim ,

I made simple samples and you can check the results below:

Measure = var _step1 = ADDCOLUMNS('Table',"sum",SUMX(FILTER(ALL('Table'),[Country]=EARLIER([Country])),[Sum All Products]))
var _step2 = ADDCOLUMNS(_step1,"Divide",DIVIDE([Sum Special],[sum]))
RETURN SUMX(_step2,[Divide])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Gayatri_D05]

Hi @201TinyTim ,
I am not sure are calculating the %Proportion for Special or all products.
Any of them just use the following measure 

% = CALCULATE(SUM(All_products)/CALCULATE(SUM(All_products),ALL(Products)))

If you want to calculate for special just replace the all products wiht your special column.
also note that you would want to pass the product a, product b column in All() in order to get the deseried value.
Let me know if this works for you.

I hope I was able to resolve your issues. If yes do give it a like. 😊

 

[201TinyTim]

Sadly this doesn't work.  I have editted the original request to try help show the %proportion logic better

[Gayatri_D05]

Hi, Thanks for the detailed logic, can you please try this 

% = CALCULATE(SUM(Special_only)/CALCULATE(SUM(All_products),ALL(Products)))

I tried it on my sample data it seems to work on it. Let me know if you are facing any issue.😊

[201TinyTim]

It Generates the correct % on the Country row  but when expand the hierarchy down to view the product breakdown then those rows are still vastly off in %   even for the case where the country only has Product A and Product C (so just 1 Special product) and would expect the % to be the exact same as the country total calc which is correct of 10% it ends up showing 95% for the breakdown for A