Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
khappersett
Resolver I
Resolver I

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 measure at the end "# Skus in 20%" has a value of 863. I confirmed this by displaying it in a card. If I replace [# Skus in 20%] in the above code with the value 863, it works beautifully, but when I have the measure in it, it is only summing the first row of data. I'm not sure why since it works when I plug in the actual number. Thanks!

1 ACCEPTED SOLUTION

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

 

View solution in original post

4 REPLIES 4
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.