Forum Discussion

dzuurman's avatar
dzuurman
Helper I
5 years ago
Solved

% difference between columns in matrix table

I have a matrix table, in which users can specify which years they want to compare the months of. Say in this case 2019 vs 2020

Of course having the totals of those months show up is easy enough. But I want to add a column in between the years showing the % difference between the two values. 

 

I have searched online a great deal but the answers weren't forthcoming or I may have misunderstood them.  Any help will be greatly appreciated.

 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi dzuurman ，

     

    Suppose your table is like below:

    Create a measure as below:

    Measure = IF(ISINSCOPE('Table'[Year]),SUM('Table'[Amount]),
var _2019=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Year]=2019))
var _2020=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Year]=2020))
Return
_2020-_2019)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    dzuurman  In case you want grand total to diff of last two years.

     

    You need to have mesure like

     

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

    Then use

    if(not(isinscope('Date'[Year])) && isinscope('Date'[Month]) ,[Diff %] , [This Year])

     

     

    This year =SUM(Sales[Sales Amount])

    Last Year = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Last Year= CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi dzuurman ，

     

    Suppose your table is like below:

    Create a measure as below:

    Measure = IF(ISINSCOPE('Table'[Year]),SUM('Table'[Amount]),
var _2019=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Year]=2019))
var _2020=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Year]=2020))
Return
_2020-_2019)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!