Forum Discussion
Matrix measures on rows & calculated columns
Hi, I was wondering if this is possible,
I have a matrix with measures on rows and year on columns, so the columns are 2016, 2017 for example. Each row ahas a measure showing a number for 2016 and 2017 for each measure. Is it possible to add a third calculated column to show for example, the difference in the measure between 2016 and 2017? Here is an example of what I mean:
Thanks in advance!
Hi djk1000,
In the matrix, when we add a measure to the matrix Values section, the measure values will display in each column group. It means the within 2016 group there will have a column, as well as the 2017 group. What we can do is making the columns under 2016 and 2017 group display blank, rather than remove them.
In your scenario, you can create a measure like below:
DiffPercentage = var Y2016= CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Year]=2016))
var Y2017= CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Year]=2017))
return
IF(SUM(Table1[Value])=SUMX(ALL(Table1),[Value])||SUM(Table1[Value])=SUMX(FILTER(ALL(Table1),[Category ]=MAX(Table1[Category ])),[Value]),DIVIDE(Y2017-Y2016,Y2016),BLANK())Best Regards,
Qiuyun Yu
8 Replies
Hi,
I was wondering if this solution did really work for you? I have a similar matrich with approximately 20 measures in the rows (values displayed on rows), but not sure how to get now a column with the differences. If I would add the 'difference' cloumn also to the 'values' section it would appear on the row as well right?
Regards,
Siem
Hi djk1000,
In the matrix, when we add a measure to the matrix Values section, the measure values will display in each column group. It means the within 2016 group there will have a column, as well as the 2017 group. What we can do is making the columns under 2016 and 2017 group display blank, rather than remove them.
In your scenario, you can create a measure like below:
DiffPercentage = var Y2016= CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Year]=2016))
var Y2017= CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Year]=2017))
return
IF(SUM(Table1[Value])=SUMX(ALL(Table1),[Value])||SUM(Table1[Value])=SUMX(FILTER(ALL(Table1),[Category ]=MAX(Table1[Category ])),[Value]),DIVIDE(Y2017-Y2016,Y2016),BLANK())Best Regards,
Qiuyun YuI am trying to following what is in the posting and have got a far as below, it is coming up with a syntax for ',' is incorrect, I cannot see where the problem lies.
DiffPercentage = var Y2018=CALCULATE(SUM('Debtor Invoice Lines'[LINETOTAL]),FILTER('Debtor Invoice Lines','Debtor Invoice Lines'[Year]=2018)), var Y2019=CALCULATE(SUM('Debtor Invoice Lines'[LINETOTAL],FILTER('Debtor Invoice Lines','Debtor Invoice Lines'[LINETOTAL]=2019))
I don't think that will work, this is in a matrix and the 2016 & 2017 headings are from the year column in the date dimension. so the calculations are on row and the date dimension is on columns, I basically want to add a third 'Year' column that shows the difference between the other two.
Thanks,
Hi djk1000,
if I understood correctly, you can use SUMX to calculate on rows instead of SUM, "SUMX ( table ; table[column] )", on the other hand, if doesn't work, you can create a measure to filter 2017 and another to filter 2016, and then invoke in this measure instead of use Sum.
