Matrix measures on rows & calculated columns

[djk1000]

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!

[Anonymous]

I want to filrer on count of mergerd column where it is greater than 1 - this is count of various underlying items. If i am applying filter on field it is taking adhoc- here i want to filter on subtotal

[siemdevlieger]

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

[v-qiuyu-msft · Accepted solution]

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 

[Anonymous]

ho dow you hide the blank columns? 

[yvonnedlr]

I 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))

[Anonymous]

Try a measure like this, or adjust it:

Δ 2017/2016 = 
CALCULATE (
    DIVIDE (
        SUM('table'[2017]);
        SUM('table'[2016])
    )
        - 1
)

... and then convert to %

[djk1000]

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,

[Anonymous]

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.