Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Matrix Visual: Subtracting two Columns

Hello,   Despite their being a few solutions I have seen to this same question, none seem to work for me. I simply want on new column giving me the data for 2021-2020, which is a simple varaince an...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous ,

    You could create a measure for each year separately to get the sum value and place them in the matrix as values. Then you could use these two measures to get the variance value and also place it as value in the matrix.

     

    2020 = SUMX(FILTER('Table','Table'[Year]=2020),'Table'[Value])

2021 = SUMX(FILTER('Table','Table'[Year]=2021),'Table'[Value])

Variance = [2021] - [2020]

     

    Example:

    Best Regards,

    Community Support Team _ Jing Zhang

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

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

You need to remove the Year column from the visual to see a single Variance column.  Of course, you would see only one Value column too.  You will need to make two table visuals (one for Value with Year) and one for Variance without year).  You may be able to overlap them to hide the reporting period column on the 2nd table with Variance to get the desired look.

 

You can also tweak your Variance measure so it shows blank for one of the two year with an IF(), but the column would still be present.

 

Regards,

Pat