Forum Discussion

julpol's avatar
julpol
Regular Visitor
2 years ago
Solved

Multiple variance calculation in matrix

Hi everyone   I'm doing a report in which I have multiple years as columns and some categories as rows. I would like to see two variance columns at the end, first as a difference between the last y...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi julpol ,

     

    Did I solve your problem? Or you can also try turning off the following two options:

     

    Best regards,
    Community Support Team_ Scott Chang

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi julpol ,

 

It will give the same results as the last screenshot of your initial post, I made simple samples and you can check the results below:

Variance$ = var _t = ADDCOLUMNS('Table',"pre",MAXX(FILTER(ALL('Table'),[Category]=EARLIER([Category])&&[Year]=EARLIER([Year])-1),[Value]))
RETURN MAX('Table'[Value])-MAXX(_t,[pre])

Variance% = var _t = ADDCOLUMNS('Table',"pre",MAXX(FILTER(ALL('Table'),[Category]=EARLIER([Category])&&[Year]=EARLIER([Year])-1),[Value]))
RETURN DIVIDE(MAX('Table'[Value])-MAXX(_t,[pre]),SUM('Table'[Value]))

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

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

julpol's avatar
julpol
Regular Visitor
2 years ago

Hi Anonymous thank you for your help. Is it possible to change the matrix so it does not show the variance fields as values per each Year column? I would like only the value from the "value" field to be visible per each year and then at the end see two variance columns.

Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi julpol ,

     

    Is this your desired result?

     

    Variance% = var _t = ADDCOLUMNS('Table',"pre",MAXX(FILTER(ALL('Table'),[Category]=EARLIER([Category])&&[Year]=EARLIER([Year])-1),[Value]))
RETURN IF(ISINSCOPE('Table'[Year]),BLANK(),DIVIDE(MAX('Table'[Value])-MAXX(_t,[pre]),SUM('Table'[Value])))

Variance$ = var _t = ADDCOLUMNS('Table',"pre",MAXX(FILTER(ALL('Table'),[Category]=EARLIER([Category])&&[Year]=EARLIER([Year])-1),[Value]))
RETURN IF(ISINSCOPE('Table'[Year]),BLANK(),MAX('Table'[Value])-MAXX(_t,[pre]))

     

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    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
    2 years ago

    Hi julpol ,

     

    Did I solve your problem? Or you can also try turning off the following two options:

     

    Best regards,
    Community Support Team_ Scott Chang