Substracting rows with dax, using groups

[Anonymous]

Hi all,

I have seen this question a few times on the board, but I could not figure it out on my own.

I have a table with 3 relevant columns within Power BI ( I recreated them in Excel to as an example of my desired result). I would like to calculate the difference between values in the column "amount" (difference between 4 weeks , see column "age").

There are multiple groups in the row "age" and "amount" and I need to make the calculation for every group seperately.

In the excel pictures below I used the average of the amount per age for substracting because the values are all the same per age. An example of the result I want can be seen in column "difference".

I hope everything is clear.

Thanks in advance!

[v-yuta-msft · Accepted solution]

@Anonymous ,

To reproduce your senario in power bi. You can create a calculate column using DAX like pattern below:

Result =
VAR Current_Group = Table[Group]
VAR Previous_Group = Current_Group - 1
VAR Current_Average =
    CALCULATE (
        AVERAGE ( Table[amount] ),
        FILTER ( Table, Table[Group] = Current_Group )
    )
VAR Previous_Average =
    CALCULATE (
        AVERAGE ( Table[amount] ),
        FILTER ( Table, Table[Group] = Previous_Group )
    )
RETURN
    Current_Average - Previous_Average

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.