Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Variance column in matrix

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 ...
Need Help
v-easonf-msft's avatar
v-easonf-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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...