Forum Discussion
How to dynamically populate a column based on the slicer selection?
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 !!
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
5 Replies
- amitchandak
Super User
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
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?
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
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 !!
