Sum by category two variants

[kimalto452]

Hi, I'm new with powe bi. I don't understand why this code work

Total price by category=

VAR category=[group]

RETURN CALCULATE(  SUM ([price]), FILTER (table1,[group]=category) )

and this code dosen't work

Total price by category=

CALCULATE(  SUM ([price]), FILTER (table1,[group]=[group]) )

[Ashish_Mathur]

Hi,

In the second formula, what do you mean by [group]=[group]

[Greg_Deckler · Accepted solution]

@kimalto452 - This is some context. Probably the biggest learning curve with DAX is understanding the context. It is literally the most important DAX concept. As such, it deserves a thorough explanation without anything vague.

Therefore, this is a calculated column for the row context to apply (see article). therefore

Consider this code:

VAR category[group]

RETURN CALCULATE( SUM ([price]), FILTER (table1,[group]-category) )

What this is saying is:

1. Get the value of the "group" column within the current row
2. Now, SUM the price column for each row in the table by comparing the value of the group column in that row with this value that is stored in the variable "category", if they match, then add them

This will return what was intended. Give me a sum of the price column for all rows in this table that match the same group value as the current row.

In front of this code:

Total price by category ?

CALCULATE( SUM ([precio]), FILTER (tabla1,[grupo]-[grupo]) )

What this says is:

1. Sum the price column for each row in the table by comparing the value of the group column in that row with the group column value of that same row and, if they match, its sum

So that's why you get the total sum for all rows in the second formula because obviously the group column in each row matches the group column in that row.

Now, consider this formula:

Columna 3 - CALCULATE(SUM([price]),FILTER('Table (13)',[group]-EARLIER([group])))

This formula generates the same value as the first calculation. This is because EARLIER causes the calculation to reference a "previous" context outside the current context, which means that it refers to the row context of the original row and not to the current row context of each row.

Let's hope this explains it well. Context is the most important concept when it comes to DAX and deserves to be explained thoroughly and completely when it comes to it. If you can master the context, you will master DAX.

I do have questions.

BTW: This is an extremely smart question to ask! Praise!

[amitchandak]

@kimalto452 , to check this try it like this and check the values

VAR category=[group]
RETURN CALCULATE( [group], FILTER (table1,[group]=category) )


Total price by category=
CALCULATE( [group], FILTER (table1,[group]=[group]) )

in my understand you will get some value in all lines, in the second case you should get different values. row context should apply