How to have variance instead of total for column subtotal in Matrix

[Anonymous]

Hi Team,

I am new to power BI. I am using matrix visualization to show the average of data for departments for the two years. Below is the screenshot of sample data.

My requirement is that, instead of column subtotal i want to have variance of the data. i.e data of 2021 subtracted from data of 2022(e.g for division A: 56.57 - 20) and show the difference instead of total. The data displayed in matrix  is the average of the
data divisionwise for that particular year.

Thanks!

[amitchandak]

@Anonymous , Make sure you are having a separate year/date table and create a diff measure

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

You can use allselected in this case

This Year = CALCULATE(sum('Table'[Qty]),filter(allselected('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(allselected('Date'),'Date'[Year]=max('Date'[Year])-1))

Then you need a meausre like

if(not(isinscope('Date'[Year])), sum('Table'[Qty]),[diff ])

Make sure you use year in visual from date/year table

IsInScope - Switch measure at different levels: https://youtu.be/kh0gezKICEM

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[rajulshah]

@Anonymous ,

Can you please share your structure or sample data?

[Anonymous]

Hi @rajulshah 
here is screenshot of my sample data.

Thanks!