Matrix table, columns based on different average

[Anonymous]

I am working with a matrix table and I want the columns to show the individual detail as well as average.

For example I want the matrix table to show me the average sales over the past 10 years, the 5 year average, and sales from 2021, 2020 and 2019. 

Basically I am envisioning a matrix table with 5 columns, one for each of the categories mentioned above (10 yr avg where 2011-2020, 5 yr avg where 2016-2020, 2021, 2020, and 2019).

I have only figured out how to add the 10 yr column (see below). I do not know how to show the other categories. What would the best way to do this be?

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You need to create 5 measures for these 5 columns and put all of them in Values field area of the matrix. Share some sample data and we can help create measures based on that. 

For example, if your data has Year column, you can use measures like

2011-2020 = DIVIDE(CALCULATE(SUM(Sales[Sales]),Sales[Year]<YEAR(TODAY()),Sales[Year]>=YEAR(TODAY())-10),10)


2016-2020 = DIVIDE(CALCULATE(SUM(Sales[Sales]),Sales[Year]<YEAR(TODAY()),Sales[Year]>=YEAR(TODAY())-5),5)


2021 = CALCULATE(SUM(Sales[Sales]),Sales[Year]=YEAR(TODAY()))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[lbendlin]

@Anonymous These additional matrix "columns" actually have to be based on measures, and they have to be placed in the Values area, not the columns area.