Filter context within summarize

[Frankkk]

Dear Team,

I created a measure to calculate avg at the customer lvl but modified filter context, so it could work on other categories.

This measure is working as expected but only on "ch" category. The goal is to modify the filter context so values "ch" values would also populate on other categories.

I tried multiple options and the best shot was to put this logic into variable, so it could populate on other categories, but the outcome numbers are clearly wrong

Could you please advise?

[Anonymous · Accepted solution]

Hi @Frankkk 

To modify the filter context in your DAX measure so that the "ch" category values are applied across other categories, we need to carefully manage the calculation context. The key is to calculate the "ch" category values independently from the context of other categories. Here's how you can adjust your DAX measure to achieve this:

VAR chQuantity =
CALCULATE(
SUM(fact[Total quantity]),
dim_product[Category] = "ch",
REMOVEFILTERS(dim_product)
)

VAR darkQuantity =
CALCULATE(
SUM(fact[Total Quantity]),
dim_product[Theme] = "dark"
)

VAR init =
SUMMARIZE(
fact,
fact[Customer Id],
"Avg",
DIVIDE(
chQuantity,
darkQuantity,
BLANK()
)
)

RETURN
AVERAGEX(init, [Avg])

VAR chQuantity: This variable calculates the total quantity for the "ch" category across all data, removing any existing filters on 'dim_product' to ensure it isn't affected by other category filters.

VAR darkQuantity: This calculates the total quantity for the "dark" theme. Here, we retain the natural filter context because we want the theme filter to apply only as needed.

Best Regards,

Jayleny

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

[ryan_mayu]

could you pls provide some sample data and expected output?