Forum Discussion

shzyincu's avatar
shzyincu
Icon for Helper I rankHelper I
9 years ago

% of change in Matrix Table

I have used matrix table to show difference of sales in months of years 2016, 2017, now i have to add new column that shows % of change compared to same months every year.   Attached is the image o...
Need Help
sumit4732's avatar
sumit4732
Icon for Advocate II rankAdvocate II
9 years ago

Hi shzyincu,

 

You can use the below measure :

#diff = ( CALCULATE(SUM(test[Value]),FILTER(ALL(test[year]),test[Year]=2016))/CALCULATE(SUM(test[Value]),FILTER(ALL(test[year]),test[Year]=2017)))-1

 

But the problem with that will be is that when you pull it Values in matrix, it will show two columns with same value.

I will suggest you to make 3 differnet measure and use it in in table or Matrix

1. for Year 2016 

   CALCULATE(SUM(test[Value]),FILTER(ALL(test[year]),test[Year]=2016))

2. for year 2017

   CALCULATE(SUM(test[Value]),FILTER(ALL(test[year]),test[Year]=2017))

3. for diff

 %diff = ([for Year 2017]/[for Year 2016])-1

 

Hope this helps 

 

-Sumit

 

shzyincu's avatar
shzyincu
Icon for Helper I rankHelper I
9 years ago

sumit4732 thanks for your reply, i tried both and both adds columns in both years :( what should i do?

 

  • sumit4732's avatar
    sumit4732
    Icon for Advocate II rankAdvocate II
    9 years ago

    Hi shzyincu,

     

    I was suggesting that you create all 3 measure as mention in past reply and then pull all of them in Values, and let the column field be empty 

     

    -Sumit 

    • shzyincu's avatar
      shzyincu
      Icon for Helper I rankHelper I
      9 years ago

      sumit4732 I did the expressions seperately and then made the third field, but still, what you mean by keep the column field empty? kindly if you can explain in detail?

      • sumit4732's avatar
        sumit4732
        Icon for Advocate II rankAdvocate II
        9 years ago

        Hi shzyincu,

         

        If you are goin for Metrix, there are three fields:

        1. Rows : put the column with month name here 

        2. Columns : dont place any column/measure here

        3. Values : put the three measures that you have created [for Year 2016 ], [for Year 2016 ], [for Diff]

         

        You can just use a simple table also now, just pull Month, [for Year 2016 ], [for Year 2016 ], [for Diff] in values.

         

        Hope this helps

        -Sumit