Forum Discussion

dzuurman's avatar
dzuurman
Icon for Helper I rankHelper 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. Bu...
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!

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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.