cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shzyincu Regular Visitor
Regular Visitor

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

matrix.png

 

 

Please if someone can guide me.

9 REPLIES 9
sumit4732 Regular Visitor
Regular Visitor

Re: % of change in Matrix Table

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 Regular Visitor
Regular Visitor

Re: % of change in Matrix Table

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

 

matrix.png

sumit4732 Regular Visitor
Regular Visitor

Re: % of change in Matrix Table

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 

Highlighted
shzyincu Regular Visitor
Regular Visitor

Re: % of change in Matrix Table

@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 Regular Visitor
Regular Visitor

Re: % of change in Matrix Table

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 Regular Visitor
Regular Visitor

Re: % of change in Matrix Table

@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 Regular Visitor
Regular Visitor

Re: % of change in Matrix Table

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

 

 

 

 

 

shzyincu Regular Visitor
Regular Visitor

Re: % of change in Matrix Table

@sumit4732 that's fine, no need of appologies Smiley Happy

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.

matrix.png

sukumararumugam Frequent Visitor
Frequent Visitor

Re: % of change in Matrix Table

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