Forum Discussion
Substracting rows with dax, using groups
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!
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_AverageCommunity 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.
1 Reply
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_AverageCommunity 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.