measure in table for individual row compared to total

[Anonymous]

I am looking to create a measure to go into a table for the example data and desired result below. The measure I need would be the % Lift column. It would be the % for the individual item compared to the Total for Category B

Category	Item	%	Total B	% Lift
A	1	50%	30%	67%
A	2	55%	30%	83%
A	3	60%	30%	100%
A	4	65%	30%	117%
A	5	70%	30%	133%
subtotal A		60%
B	6	20%
B	7	25%
B	8	30%
B	9	35%
B	10	40%
subtotal B		30%

[v-yuta-msft]

@Anonymous ,

I suppose the [%] is column, not measure, you can create a measure using DAX below:

% Lift = 
VAR Total_B = CALCULATE(AVERAGE('Table'[%]), FILTER(ALL('Table'), 'Table'[Category] = "B"))
VAR Current_Percentage = MAX('Table'[%])
VAR Current_Category = MAX('Table'[Category])
RETURN
IF(Current_Category = "A", (Current_Percentage / Total_B) - 1, BLANK())

  

Community Support Team _ Jimmy Tao

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

[Anonymous]

Unfortuately the % calculation is a measure that is based on variables so it cannot be made into a calculated column

[v-yuta-msft]

@Anonymous ,

Could you share the dax formula of measure [%]?

Regards,

Jimmy Tao

[Anonymous]

% = DIVIDE([SALES],[GOALS])

[v-yuta-msft]

@Anonymous ,

"The measure I need would be the % Lift column. It would be the % for the individual item compared to the Total for Category B"

<--- Could you please use some simple formula or expression to clarify this logic?

Community Support Team _ Jimmy Tao

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

[Anonymous]

% lift = (% / Total B) - 1