Forum Discussion
Adding dynamic versus columns in a matrix table
Hi,
a customer ask me to build a table with vs columns in the table :
the full table will be about Sales, in row we have a country field.
the customer wants that kind of columns :
2020, 2021, 2022, 2021 vs 2020, 2022 vs 2021
(2020 means this columns should contains Sales of year 2020, 2021 vs 2020 means this columns should contains Sales of year 2021 - Sales of Year 2020) .
How can I build this table ?
I manage to do this by using static measures by Column :
for example for column 2020, I create the following measure for the column :
Sales_2020=CALCULATE(Sales[Sales], sales_table[YEAR]=2020)
then for 2021vs2020 I create the following measure for the column :
Sales_2021_2020 = [Sales_2021]-[Sales_2020]
It works, but it is not dynamic enough :
- Customers would also like to change the indicator in the table (uses expenses instead of Sales for example).
- Years will change (we can add 2019 data for example, or next Year we will have 2023 data) and the table should adapt automatically (adding columns or at least changing year in columns).
- (bonus) same questions with % diffence (exemple % difference between 2021 and 2020).
Do you have some ideas on how to handle that, the most dynamic way ?
Best regards
Bebs , You can make it dynamic then you will not get the year name example
Sales_2 year behind=CALCULATE(Sales[Sales], sales_table[YEAR]=year(today()) -2 )
You can check if the hybrid column can help
if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc
2 Replies
- amitchandak
Super User
Bebs , You can make it dynamic then you will not get the year name example
Sales_2 year behind=CALCULATE(Sales[Sales], sales_table[YEAR]=year(today()) -2 )
You can check if the hybrid column can help
if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc - Bebs
Helper II
I needed a little time to understand it, but yes, it solved most of the problem, thanks 🙂