formula to calculate % of column total for multiple hierarchies

Anonymous — Thu, 21 Jan 2021 20:26:37 GMT

Hello Everyone,

I am trying to create a measure for market share (% of column total) to be used in a matrix. This is an example of my expected result - each hierarchy should be calculated against the column total in the specific region.

All the formulas I have tried either calculate the % over grand total and not the region specific column total. My current formula only produces correct %s for the first 2 hierarcies (segment/subsegment). If anyone has worked with this before, I will appreciate any feedback. Thank you so much in advance.

VAR currentsale = [Sales]
VAR productsales = CALCULATE([Sales], ALLSELECTED('Segment'))
VAR subsegmentsale = CALCULATE([Sales], ALLSELECTED('Segment'))
VAR segmentsale = CALCULATE([Sales], ALLSELECTED('Segment'[segment]))
VAR grandsale = CALCULATE([Sales])

VAR Result = IF(ISINSCOPE(Series[product]), DIVIDE(currentsale,modelsales),
IF(ISINSCOPE('Segment'[subsegment]), DIVIDE(currentsale,manufacturersale),
IF(ISINSCOPE('Segment'[Segment]),DIVIDE(currentsale,groupsale),
DIVIDE(currentsale, grandsale))))

RETURN
Result

Re: formula to calculate % of column total for multiple hierarchies

amitchandak — Fri, 22 Jan 2021 03:51:36 GMT

Anonymous , You have the option of % of the total, So add the measure again and right on the measure on in visualization pane and use % of total

If region 1 is because of value (means row value in region) a measure like this

% of total = divide([measure] , calculate([measure],allexcept(Table, Table[region]))

if region 1 is column

% of total region 1= divide([measure] , calculate([measure],allt(Table[region1]))

% of total region 2= divide([measure] , calculate([measure],allt(Table[region2]))

Also refer : https://www.youtube.com/watch?v=6jTildcV2ho

topic Re: formula to calculate % of column total for multiple hierarchies in DAX Commands and Tips

formula to calculate % of column total for multiple hierarchies

Re: formula to calculate % of column total for multiple hierarchies