Forum Discussion
Difference between rows by grouping with multiple columns
Hi All,
I am quite new to Power BI.
I have a table where I have to calculate the difference between previous yr and current yr value by grouping data by different attributes.
| Year | Univ | School | Class | Value | Difference |
| 2020 | ABC | sA | cX | 10 | -30 |
| 2019 | ABC | sA | cX | 40 | 10 |
| 2018 | ABC | sA | cX | 30 | null |
| 2018 | XYZ | sB | cY | 40 | -40 |
| 2017 | XYZ | sB | cY | 80 | null |
| 2016 | XYZ | sC | cZ | 10 | null |
| 2017 | XYZ | sC | cZ | 30 | null |
| 2017 | ABC | sD | cZ | 70 | null |
The difference column should be calculated for each yr by grouping the value by Univ, School and Class.
I basically want to replicate the sql query in DAX :
SELECT Year,Univ, School,Class, Value,
Value - LAG(Value) OVER (PARTITION BY Univ, School,Class ORDER BY Year )
AS Difference FROM TableA
Note : I am using DirectQuery to the Database.
I want to calculate the difference column.
Hi Anonymous
First , you need create a measure to return the previous value .
PreviousValue = CALCULATE(SELECTEDVALUE('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Univ],'Table'[School]),'Table'[Year]=SELECTEDVALUE('Table'[Year])-1))Then create a measure to count the diff between previous year and current year .
Diff = IF([PreviousValue] <> BLANK(),SELECTEDVALUE('Table'[Value])-[PreviousValue],BLANK())Finally, put these two measures in your table visual , you will get a result like below .
Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Anonymous
when using power bi visuals, aggregation happens automatically. The only thing which is required in your case is to move the year column filter one year back.
Value = SUM ( TableA[Value] )Value Previous Year = VAR CurrentYear = MAX ( TableA[Year] ) RETURN CALCULATE ( [Value], TableA[Year] = CurrentYear - 1 )Difference = [Value] - [Value Previous Year]Hi Anonymous
First , you need create a measure to return the previous value .
PreviousValue = CALCULATE(SELECTEDVALUE('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Univ],'Table'[School]),'Table'[Year]=SELECTEDVALUE('Table'[Year])-1))Then create a measure to count the diff between previous year and current year .
Diff = IF([PreviousValue] <> BLANK(),SELECTEDVALUE('Table'[Value])-[PreviousValue],BLANK())Finally, put these two measures in your table visual , you will get a result like below .
Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
