Forum Discussion
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 analysis for each accounting preiod in the table
By creating this measureit gives me two identical columns! I just need one! Just so you know my 2021 and 2020 Data table are all on the same Column as Value
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.
3 Replies
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.
Hello Jing Zhang,
Thank you very much! For those of you who see this solution you have to enter the measures below seprately!
2020 = SUMX(FILTER('2020 & 2021 FYS','2020 & 2021 FYS'[Year]=2020),'2020 & 2021 FYS'[Value])2021 = SUMX(FILTER('2020 & 2021 FYS','2020 & 2021 FYS'=2021),'2020 & 2021 FYS'[Value])Variance = [2021] - [2020]
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
