Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |