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 ,

 

There is no problem with your calculation logic. The problem is because you are putting multiple fields in the Values field of the matrix.For an matrix. multiple fields can be put in the row, which represents the hierarchy, whereas both columns and Values should have only one aggregated field.

 

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 ,

 

re 1) - the variance$ has probably a wrong filter as its value is $0? Please refer to first screenshot where only one variance is used in teh Values field, and the column shows $0 across all rows.

 

re 2) - is there a way to achieve this? To have two extra calculated columns on the right, one $ variance and one % variance.

 

Thank you

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