Forum Discussion

khappersett's avatar
khappersett
Resolver I
9 years ago
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...
Need Help
  • khappersett's avatar
    khappersett
    9 years ago

    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))) 

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

khappersett's avatar
khappersett
Resolver I
9 years ago

I have tried the formula as both a calculated column and a measure. I used this formula for both:

 

Number of SKUs in 20% = ROUNDUP(CALCULATE([Number of SKUs]*0.2), 0)

The measure gives me the correct number (863 in this case), but when I do the same formula as a calculated column, it is giving me 67.06k.