Trying to Understand CALCULATE and FILTER in DAX

Question

I am trying to play around with FILTER and CALCULATE DAX expressions. I would like to find out the no of distinct orders which have products whose price is less than the average cost of the category: Bikes. First I am creating a Measure to calculate the average cost of Bikes, which is 913.61

avg product cost bikes =
CALCULATE (
    AVERAGE ( AW_Product_DIM[ProductCost] ),
    AW_Product_Categories_DIM[CategoryName] = "Bikes"
)

Next, I am creating a measure to calculate the total no of orders.

Total Orders =
DISTINCTCOUNT ( AW_Sales[OrderNumber] )

Next, I have a CALCULATE Function with FILTER to calculate the total number of orders, which have products whose cost is less than the average cost of all products which is 913.61.

Sales less than Bike's avg =
CALCULATE (
    [Total Orders],
    FILTER (
        AW_Product_DIM,
        AW_Product_DIM[ProductCost] < [avg product cost bikes]
    )
)

Can you please help me understand why I am not getting any value in the Sales less than Bike's avg column? All the products in Accessories and Clothing category are less than the Bike's avg cost which is 913.61. So there should be 16983 in sales less than Bike's avg for Accessories and 6976 in Clothing. All the Bikes are more than 1000, so that should be empty.

Please refer to

Which has the Matrix view that I am trying to Visualize.

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-20T17:02:13

A measure is calculated within the context it is called from. When you call a measure inside of a FILTER (or any iterator function), it's calculated within the row context of the table you are iterating through and can be a different value for each row.

One way to remedy this is to calculate it once outside the FILTER and reuse that value for each row that FILTER iterates over like this:

Sales less than Bike's avg =
VAR AvgBikeCost = [avg product cost bikes]
RETURN
    CALCULATE (
        [Total Orders],
        FILTER (
            AW_Product_DIM,
            AW_Product_DIM[ProductCost] < AvgBikeCost
        )
    )

Trying to Understand CALCULATE and FILTER in DAX

1 Answers