Forum Discussion
Using Measure to Filter Calculate
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)))
khappersett,
The results of [# Skus in 20%] measure vary by row context, in other words, the result of [# Skus in 20%] measure is not always 863. Create a column instead of measure to calculate [# Skus in 20%], then check if the above formula returns your expected result.
For more details about context, please review this article.
Regards,
Lydia
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)))