Need help with FILTER and CALCULATE

[deagle311]

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 that 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.

Thank You.

[Fowmy]

@deagle311 

Use one of the following two formulas.

Sales less than Bike's avg = 

var _bikecost = [avg product cost bikes] return
CALCULATE (
    [Total Orders],
    FILTER (
        AW_Product_DIM,
        AW_Product_DIM[ProductCost] < _bikecost
    )
)

================================================================

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

Why you did not get the expected result is that your measure [avg product cost bikes] got evaluated within the filter function which changed the calculation as that measure got recalculated based on the current context.
By assigning it to a variable, the value is kept fixed.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[CNENFRNL]

Hi, @deagle311 , as to the measure

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

in each of row of the matrix, the Filter part evaluates in initial filter context, say [CategoryName]="Accessories" ([CategoryName] is picked from AW_Product_Categories_DIM?); AW_Product_DIM is also filtered by it during the evaluation of Filter.