% of change in Matrix Table

[shzyincu]

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 of my UI.

Please if someone can guide me.

[sumit4732]

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]

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

[sumit4732]

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]

@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]

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

[shzyincu]

@sumit4732 this does not render the desired output then, 

2. Columns : dont place any column/measure here, if i don't put YEAR field here, it sums up both years sales values in one column i need to show multiple columns for each year.

[sumit4732]

Hi,

Aplogies if I was not clear the last time.

I want you to create this three Measures,

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

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

3. %diff= ([Year2017]/[Year2016])-1

Now, in Metrix

1. Rows : put the column with month name here (Jan, Feb, etc..)

2. Columns : dont place any column/measure here

3. Values : put the three measures that you have created [Year2016 ], [Year2017 ], [%diff]

If this doesnt work please share screenshot with what proble you are facing.

-Sumit

[sukumararumugam]

@sumit4732

In the below solution you have created measures for Year2016 & Year2017, but how do we create this for dynamic year- Meaning it is not fixed.

[stephanderton2]

This can be done generically, or somewhat dynamic, by using measures to return the specific years we're interested in by creating 2 measures before the steps that @sumit4732 outlined.

Here's an example for the most recent 2 years:

(Note: I am using a "Dates" table that I've linked to the main table's date field)

CurrYear = MAX(Dates[Year])

PrevYear = CurrYear - 1

These measures are used in place of the year numbers:

1. ValuePrevYear =  CALCULATE(SUM(test[Value]), FILTER(ALL(test[year]), test[Year] = PrevYear ))

2. ValueCurrYear =  CALCULATE(SUM(test[Value]), FILTER(ALL(test[year]), test[Year] = CurrYear ))

And if some of the year fields are empty, we can modify the % calculation to avoid division by zero:

% YearDiff = IF ( ISBLANK ( [ValuePrevYear] ), 0, ( [ValueCurrYear] / [ValuePrevYear] ) - 1 )

[shzyincu]

@sumit4732 that's fine, no need of appologies 🙂

kindly check in the attachment, Sales % Change and Samples % Change are getting repeated for both 2016, 2017, i don't want to show these two columns in the year 2016, i did resized the columns to hide it, but when i upload it on server, it does show the columns in 2016. I wanna removed these two from 2016 but don't wanna skip the year itself.