REMOVEFILTER not working inside SUMX?

[Rick013]

I have a question about the REMOVEFILTER function, for me it doesnt work when it's inside a SUMX function.

Example1: Measure:
sumaverage =
SUMX(
prices_table,
CALCULATE(
MIN(prices_table[price]),
REMOVEFILTERS(stores_table[Stores])
)
)

Example 2: measure:
test =
CALCULATE(
MIN(prices_table[price]),
REMOVEFILTERS(stores_table[Stores])
)

stores_table is a connected table that is used to filters on stores. I want to remove this filter to get the minimum price for a product over all stores. The REMOVEFILTER function is not working in this instance. But it is working as expected when it is not inside a SUMX function (example 2). My actual measure is a bit more complex but i simplified it to explain the problem so ignore the actual usefullness of this measure. I've tried working with CROSSFILTER or ALL but nothing works while it is inside this SUMX()

Does anyone know what is going on here?
Thanks in advance!

[123abc]

The issue you're encountering might stem from the way context transition works in DAX and how it interacts with the SUMX function.

When you use REMOVEFILTERS or REMOVEFILTER inside a measure, it removes filters from the filter context. However, when you use it inside SUMX, the context transition behavior changes.

In your first example, when you use REMOVEFILTERS inside SUMX, it removes filters from the inner filter context created by SUMX, not from the outer filter context. This behavior might not be what you expect, leading to unexpected results.

To achieve the desired outcome, you might need to adjust the way you're using SUMX and context transition. One approach could involve using SUMMARIZE to summarize the data you need and then iterate over that summarized table using SUMX.

Here's a revised version of your measure using SUMMARIZE and SUMX:

sumaverage =

SUMX(

    SUMMARIZE(

        prices_table,

        prices_table[ProductID],

        "MinPrice", CALCULATE(MIN(prices_table[price]), REMOVEFILTERS(stores_table[Stores]))

    ),

    [MinPrice]

)

In this measure:

1. SUMMARIZE generates a table with unique ProductID values and their corresponding minimum prices across all stores.
2. SUMX iterates over each row of the summarized table and sums up the minimum prices.

This approach ensures that REMOVEFILTERS operates within the context of each row generated by SUMMARIZE, providing the desired behavior.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Rick013]

Thanks for your reply and pointing out context transiton. I have read into it and realise my exampel code was to simple to translate your solution to my problem. Please allow me to complicate the code a little bit since I fail to complete understand and translate your solution.

I actualy want to sum over all products for a store and calculate the hypothetical revenue for that store if we took the weighted average price for each product.
My prices_table contains a column for store (linked to stores_table), for product_id, for volume (number of sales), and for price

Measure1(used in a visual to show per store):
Hypothetical_revenue=
SUMX(prices_table, [volume] * [avg_price])

Measure2:
avg_price =

VAR filter_table =
CALCULATETABLE(
prices_table
,REMOVEFILTERS(stores_table[stores])

Return =
DIVIDE(
SUMX(filter_table, [Volume] * [Price]),
SUMX(filter_table, [Volume]))

Instead of giving me a correct weighted average it calculated the average over only the 1 store resulting in the average price being the same as the original price
Thanks in advance!

[123abc]

Thank you for providing more context. It seems like you want to calculate a weighted average price for each product across all stores and then use that weighted average price to calculate hypothetical revenue per store.

In your Measure2, the issue lies with the use of REMOVEFILTERS function. REMOVEFILTERS removes all filters from the stores_table, but it doesn't affect the filter context of the prices_table within the CALCULATE function. Therefore, the filter context from the outer context (store) is still applied when calculating the weighted average price.

To calculate the weighted average price across all stores, you need to remove the filter context from the prices_table, not the stores_table. You can use the ALL function on the prices_table to achieve this.

Here's how you can modify your Measure2 to calculate the weighted average price correctly:

avg_price =
VAR filter_table =
CALCULATETABLE(
prices_table,
ALL(prices_table[Product_ID])
)
RETURN
DIVIDE(
SUMX(filter_table, [Volume] * [Price]),
SUMX(filter_table, [Volume])
)

In this modified measure, the ALL function removes the filter context from the prices_table, ensuring that the weighted average price is calculated across all products regardless of any filters applied at the product level. This should give you the correct weighted average price, which you can then use in your Measure1 to calculate hypothetical revenue per store.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.