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

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

Helper I

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

Advocate II

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

Helper I

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

Advocate II

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

Helper I

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

Advocate II

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

Frequent Visitor

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

Regular Visitor

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 )

Helper I

@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

Announcements

#### Happy New Year from Power BI

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

#### Check it Out!

Click here to read more about the December 2020 Updates!

#### 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

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

Top Solution Authors
Top Kudoed Authors