Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 Schoo...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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]



 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 

    Please create as new measures not calculated columns.