Forum Discussion

kimalto452's avatar
kimalto452
Frequent Visitor
6 years ago
Solved

Sum by category two variants

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]) )
 
 
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    @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!

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    @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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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