topic Re: Difference between rows by grouping with multiple columns in DAX Commands and Tips

Difference between rows by grouping with multiple columns

Anonymous — Wed, 30 Mar 2022 10:34:25 GMT

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.

Re: Difference between rows by grouping with multiple columns

tamerj1 — Wed, 30 Mar 2022 13:00:09 GMT

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]

Re: Difference between rows by grouping with multiple columns

Anonymous — Wed, 30 Mar 2022 23:54:05 GMT

Hi tamerj1 ,

Thanks for the response.

I tried the solution you proposed but it isn't working with the direct query model.

Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.

Re: Difference between rows by grouping with multiple columns

tamerj1 — Thu, 31 Mar 2022 02:07:19 GMT

Hi Anonymous

Please create as new measures not calculated columns.

Re: Difference between rows by grouping with multiple columns

Anonymous — Wed, 06 Apr 2022 05:22:56 GMT

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.