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

 

 20182019third column for VAR
Revenue10000001200000=2019 Rev / 2018 Rev - 1
Costs200000200000etc
Profit8000001000000 

 

Cheers,

 

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

    or

    measure =

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

    return

    divide(_a,sum(2018))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak 

       

      Thanks for the quick reply.

       

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

       

      Cheers,

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