cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

matrix.png

 

 

Please if someone can guide me.

10 REPLIES 10
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 

   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

 

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

 

matrix.png

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 

@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

-Sumit

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

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

 

 

 

 

 

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

 

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.

matrix.png

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors