Difference between SUMX and Calculate in DAX Language for this specific example

[HassanAshas]

I have the following two DAX Commands, which seem to be giving different results,

First DAX command

FourLeggedSales = SUMX(
    FILTER(Purchase, RELATED('Product'[Legs]) = 4), 
    Purchase[Quantity]
    )

Second DAX Command,

FourLeggedSales = CALCULATE( SUM(Purchase[Quantity]), 'Product'[Legs] = 4)

The second DAX Command is given in the solution file and apparently gives correct results. While the first one was what I wrote and it is giving wrong results.

Can anyone help me understand what's causing the issue here?

Additional Information if Needed

Just to be clear, I am solving this problem of DAX here: https://www.wiseowl.co.uk/power-bi/exercises/dax/filtering/4094/

You can find the Example Workbook from the link provided as well (with the dataset)

^These are the two example DAX Commands I gave here.

Complete DAX Measure that I created is as given (it doesn't give correct results)

Ratio = 

var FourLeggedSales = SUMX(
    FILTER(Purchase, RELATED('Product'[Legs]) = 4), 
    Purchase[Quantity]
    )

var SixLeggedSales = SUMX(
    FILTER(Purchase, RELATED('Product'[Legs]) = 6), 
    Purchase[Quantity]
)

var SumOfSales = FourLeggedSales + SixLeggedSales

var ManyLeggedRatio = DIVIDE(SUMX(Purchase,Purchase[Quantity]), SumOfSales, 0)
Return ManyLeggedRatio

The result of the measure I created is as given,

The Measure created in the Solution File is as below,

FourLeggedRatio = 

VAR SalesFourLegs = CALCULATE( SUM(Purchase[Quantity]), 'Product'[Legs] = 4)

VAR SalesSixLegs = CALCULATE( SUM(Purchase[Quantity]), 'Product'[Legs] = 6)

VAR ManyLeggedRatio = DIVIDE(SUM(Purchase[Quantity]) , (SalesFourLegs + SalesSixLegs))

RETURN ManyLeggedRatio

Result of the Measure in the Solution File is as given,

[AlexisOlson]

I think the key fact that you may be missing is that the filter argument inside CALCULATE replaces the existing filter context for that column rather than treating it as an extra filter on that column. Note that the following are equivalent:

CALCULATE (
    SUM ( Purchase[Quantity] ),
    'Product'[Legs] = 4
)

CALCULATE (
    SUM ( Purchase[Quantity] ),
    FILTER (
        ALL ( 'Product'[Legs] ),
        'Product'[Legs] = 4
    )
)

With this formulation, the denominator (SalesFourLegs + SalesSixLegs) is the same for each cell in your matrix since the filter context from the visual row (0, 2, 4, 6) is replaced. The ALL part is what's missing in the SUMX version.

In the SUMX version you have, the Purchase table (inside FILTER) is already filtered by the visual context (it's not the whole table) and you're adding an additional filtering condition. This means that the denominator depends on which visual row you're in since there is nothing to remove the Legs filter context:

VAR FourLeggedSales =
    SUMX (
        FILTER ( Purchase, RELATED ( 'Product'[Legs] ) = 4 ),
        Purchase[Quantity]
    )

Note: This is similar to these CALCULATE versions where the visual context is preserved:

CALCULATE (
    SUM ( Purchase[Quantity] ),
    KEEPFILTERS ( 'Product'[Legs] = 4 )
)

CALCULATE (
    SUM ( Purchase[Quantity] ),
    FILTER (
        VALUES ( 'Product'[Legs] ),
        'Product'[Legs] = 4
    )
)

This is one of the situations where trying to avoid CALCULATE makes life more difficult since CALCULATE allows you to easily remove the filter context for a single column while leaving other filters in place. As @Greg_Deckler demonstrates, it's certainly possible to avoid CALCULATE but, in this case, is significantly more work, may break when your report has other slicers or page filters (if the ALL functions remove more than intended), and will be slower if you happen to be working with a large data model.

[Greg_Deckler · Accepted solution]

@HassanAshas Also, here is a No-CALCULATE solution (there are probably simpler ones):

Measure = 
    VAR __Year = YEAR(MAX('Purchase'[PurchaseDate]))
    VAR __4Legs = SELECTCOLUMNS(FILTER(ALL('Product'),[Legs] = 4),"__ProductID",[ProductId])
    VAR __6Legs = SELECTCOLUMNS(FILTER(ALL('Product'),[Legs] = 6),"__ProductID",[ProductId])
    VAR __4Table = FILTER(ALL('Purchase'),[Year] = __Year && [ProductId] IN __4Legs)
    VAR __6Table = FILTER(ALL('Purchase'),[Year] = __Year && [ProductId] IN __6Legs)
    VAR __4Sum = SUMX(__4Table,[Quantity])
    VAR __6Sum = SUMX(__6Table,[Quantity])
    VAR __Sum = SUM('Purchase'[Quantity])
    VAR __Return = DIVIDE(SUM('Purchase'[Quantity]), __4Sum + __6Sum)
RETURN
    __Return

[Greg_Deckler]

@HassanAshas I believe the main problem is that the Purchase table relationship does not filter the Products table but rather the Product table filters the Purchase table. What I don't understand is the scenario itself.