Forum Discussion
Help with basic DAX Code
[Total Sales] has to be a measure for it to work inside CALCULATE like this. How is it defined?
If it's defined as SUM ( Sales[Quantity] ) * SUM ( Sales[Net Price] ), then it's nonsensical. If it's defined as SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ), then the explanation has to do with granularity.
The FILTER removes the rows with the product under 1000, but Sales and SUMMARIZE ( Sales, Sales[Quantity], Sales[Net Price] ) have different numbers of rows. In Measure 1, you add up the product for each row in the summary table but in Measure 2, you add up each row in Sales, which contains multiple rows with the same Quantity and Net Price instead of just a single row for each distinct combination.
Measure 1 is grouping before summing while Measure 2 only has grouping inside the filter; the actual sum has no grouping by Quantity and Net Price.
Your Measure 2 give the same result as
SUMX ( FILTER ( Sales, Sales[Quantity] * Sales[Net Price] >= 1000 ), Sales[Quantity] * Sales[Net Price] )
Hi Greg_Deckler thanks a lot for your reply!
Yes, [Total Sales] is defined as you described.
The problem here is that I see both expressions as equivalent, so I need some more effort understanding evaluations contexts (thought I was doing pretty well and this is quite annoying!)
[Total Sales] has to be a measure for it to work inside CALCULATE like this. How is it defined?
If it's defined as SUM ( Sales[Quantity] ) * SUM ( Sales[Net Price] ), then it's nonsensical. If it's defined as SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ), then the explanation has to do with granularity.
The FILTER removes the rows with the product under 1000, but Sales and SUMMARIZE ( Sales, Sales[Quantity], Sales[Net Price] ) have different numbers of rows. In Measure 1, you add up the product for each row in the summary table but in Measure 2, you add up each row in Sales, which contains multiple rows with the same Quantity and Net Price instead of just a single row for each distinct combination.
Measure 1 is grouping before summing while Measure 2 only has grouping inside the filter; the actual sum has no grouping by Quantity and Net Price.
Your Measure 2 give the same result as
SUMX (
FILTER (
Sales,
Sales[Quantity] * Sales[Net Price] >= 1000
),
Sales[Quantity] * Sales[Net Price]
)Hi AlexisOlson thanks a lot for your reply!
Yes, [Total Sales] is defined as a measure: SUMX(Sales,Sales[Quantity] * Sales[Net Price])
I think I'm seeing the problem now after your explanation.
Please let me know if I interpreted you correctly:
On the Measure 1 I'm not using SUMX over Sales but over a summarized version of sales filtered by quantity * net price >= 1000.
On the Measure 2, I'm using the summarized table to filter Sales inside Calculate and only then applying SUMX on the resulting filtered Sales table. As Sales is not summarized, only filtered, I can have many rows for the same combination of quantity * net price >= 1000.
So, measure 1 has the granularity of the summarized table (less rows) and measure 2 has the granularity of sales (more rows).Am I right?Thanks again both of you.Nailed it.