Forum Discussion
Helper I% of change in Matrix Table
Advocate IIHi 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 Isumit4732 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
- 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.
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 )
