Forum Discussion

djk1000's avatar
djk1000
Frequent Visitor
8 years ago
Solved

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 a...
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

 

Try a measure like this, or adjust it:

 

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

 

... and then convert to %

 

 

 

 

djk1000's avatar
djk1000
Frequent Visitor
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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.