Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

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




Please if someone can guide me.

Advocate II
Advocate II

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 


2. for year 2017


3. for diff

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


Hope this helps 




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



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 



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

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


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



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.










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.


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 )

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


Helpful resources



We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!


The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors