Forum Discussion
Solved
Using Measure to Filter Calculate
I am trying to sum a column based on a filter and am using this code: Top 20% Margin = CALCULATE(SUM('PH FY 2016'[Net Margin]), FILTER(ALL('PH FY 2016'[sku]), [Rank] <= [# Skus in 20%])) The mea...
Finally figured this out - I needed to set the measure [# Skus in Top 20%] as a variable before calling it in my filter. Here is the working code:
Top 20% Margin = VAR top20 = [# Skus in Top 20%] RETURN (CALCULATE(SUM('PH FY 2016'[Net Margin]), FILTER(ALL('PH FY 2016'[sku]), [Rank] <= top20)))
I have also tried using the sum and sumx functions, but still can't seem to get the correct value from the calculated column.
Finally figured this out - I needed to set the measure [# Skus in Top 20%] as a variable before calling it in my filter. Here is the working code:
Top 20% Margin = VAR top20 = [# Skus in Top 20%] RETURN (CALCULATE(SUM('PH FY 2016'[Net Margin]), FILTER(ALL('PH FY 2016'[sku]), [Rank] <= top20)))