How to dynamically populate a column based on the slicer selection?

[Anonymous]

Hi Everyone,

My requirement is to dynamically show a column based on the slicer selection. For Example, Let us consider this to be the Sample data. We need to show the Last 3 Years Average Values as an individual column in a Matrix visual. 

Year	Area	Score
2019	A1	5
2019	A2	2
2020	A1	3
2020	A2	4
2020	A3	2
2020	A4	5
2020	A5	3
2021	A1	2
2021	A2	3
2021	A3	1

We have Year, Area and Scores assigned for each Area (Out of 5). We need to Calculate the Average Score Values for each Year and that should shown as an individual column. My Output should look like the below in the Matrix Visual. Each Year Name should be created as a new column and corresponding average value should be shown.

REQUIRED OUTPUT TABLE:

Area	2019	2020	2021
A1	3.5	3	2
A2	0	3	2
A3	0	4	0

In the Year Slicer, If I select "2021", it should correspondingly display the output as shown above. If we cant able to show the Last Year's data, then we need to show that corresponding slicer selection data. (Say), If I select 2019 in Year slicer, it should display the output as below.

Area	2019
A1	3.5
A2	0
A3	0

Help me in creating the dynamic Columns as 2019, 2020, 2021(shown in the required Output Table) etc for each Year's Average value and filter it based on the slicer selection. And Is it possible to show the Last 3 Year's data in Matrix Visual?

This is an urgent requirement. Please help me with your solution in PBIX file or DAX query, that would be helpful.

Thankyou in advance !!

[Anonymous]

Hi Everyone,

Please do post your solutions over here, so that I can able to sort out this issue asap.

Thanks!!

[Anonymous · Accepted solution]

Hi @Anonymous ,

First, please create a year dimension table(don't create any relationship with your fact table).

Years = VALUES('Table'[Year])

Then create a measure as below to get the average of score. You can find all details in the attachment.

Average of score = 
CALCULATE (
    AVERAGE ( 'Table'[Score] ),
    FILTER ( 'Table', 'Table'[Year] <= SELECTEDVALUE ( 'Years'[Year] ) )
)

Best Regards

[Anonymous]

Hi @Anonymous

Thankyou very much for a quick solution on this issue. But to add on to that, what if we have more than 1 measure to display in that same matrix chart?

In that case, the column values of the year are not dynamically populating as last 3 years based on slicer selection. I need the same exact solution you have provided (i.e) The column name should be 2021, 2020 & 2019 , if we select 2021 in the Year selection, along with some other measures too. I have added a measure called "Global Avg", if we add that measure, then Year name changing as a column header. That should not happen as per the requirement.

Global Avg is calculated as follows,

Global Avg = CALCULATE(AVERAGE('Table'[Score]),ALLEXCEPT('Table','Table'[Area],'Table'[Year]))

The expected output is,

Area	2019	2020	2021	Global Avg
A1	3.5	3	2	3
A2	0	3	2	2.5
A3	0	4	0	4

Can you please help me in achieving this asap? This needs to be fixed in a short notice. Your guidance will be really helpful. 

Thanks !!

[amitchandak]

@Anonymous , is this not a matrix display with year on slicer and year on matrix column?

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

[Anonymous]

@amitchandak , Year is a slicer and we need the Output as Average values for each Year with its corresponding Areas. Can we able to achieve the required logic which I mentioned in the above problem statement?