Variance column in matrix

[Anonymous]

Hi,

How can I create a third column in a matrix to show variance YOY, without creating individual measures for reach value of Revenue, Costs, Profit, etc.?

	2018	2019	third column for VAR
Revenue	1000000	1200000	=2019 Rev / 2018 Rev - 1
Costs	200000	200000	etc
Profit	800000	1000000

Cheers,

[v-easonf-msft]

Hi, @Anonymous 

Here ,we can use a measure as below to work around:

Measure =
var sum18 = CALCULATE(SUM('Table'[values]),FILTER('Table', 'Table'[Year] =2018))
var sum19 = CALCULATE(SUM('Table'[values]),FILTER('Table','Table'[Year]=2019))
return
IF(ISINSCOPE('Table'[Year]),SUM('Table'[values]),sum19/sum18-1)

Besides, you can change the name of  “subtotals label”  from “Total”  into  “third column for Var”  in “format”.

Here’s a sample I made:

url:https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EQBlSvY6aPNApY8nYaDQ5dAB17Pd6KzNSc3UjfwH7Pj_xA?e=RnPL4d

Best Regards,

Community Support Team _ Eason Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-easonf-msft,

This would work... but my data is structured differenty.

In my dataset, I have a column for Revenue, Costs & Profit.  So I need the measure to calculate the variance of that row, I cannot specify I column as there are 3 different columns... 

[amitchandak]

= divide(sum(2019)-sum(2018),sum(2018))

or

measure =

var _a =sum(2019)-sum(2018)

return

divide(_a,sum(2018))

[Anonymous]

Hi @amitchandak 

Thanks for the quick reply.

I'm not sure how I can do "sum(2019)"?

Cheers,

[amitchandak]

Please share your table structure

[Anonymous]

@amitchandak ,

See below screenshot... will this work?  or how else can I share my table structure?

I think one of the obstacles here is that each of the rows in the matrix come from different columns in the data table... which means the DAX formula for the variance cannot reference a specific column, but needs to reference the row it is on.

Cheers,

Dan