DAX – Top N for each Previous & Current Year
This article covers a real-time scenario which was asked by one of the customers.
Requirement:
Show the sales of the top 5 countries for the current year and previous year.
Challenges:
The above requirement seems to be simple, but we don’t have the same customers on the previous and current year.
- If we use a simple bar/column chart, then we will get multiple standalone bars or columns.
- We can use a stacked column chart but if we apply the top 5 then it will apply on the whole dataset, but we need to apply for each year.
Solution:
Follow the below steps
- Create a DAX table with below logic
- TOP 5
- Filter – Current Year & Previous Year
- Union the above results
Detailed Steps:
I am using below dataset (Sales).
| Country | Year | Value |
| A | 2018 | 12 |
| B | 2018 | 34 |
| C | 2018 | 56 |
| D | 2018 | 17 |
| E | 2019 | 17 |
| F | 2019 | 19 |
| G | 2019 | 11 |
| H | 2019 | 23 |
| I | 2017 | 16 |
| J | 2017 | 18 |
| K | 2017 | 23 |
| L | 2017 | 26 |
| M | 2017 | 56 |
Used the DAX syntax to create another table
_Top5 = UNION(
CALCULATETABLE(
TOPN(5,
SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])
,Sales[Value], DESC),
Sales[Year]=FORMAT(YEAR(TODAY()),”0000″)),
CALCULATETABLE(
TOPN(5,
SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])
,Sales[Value], DESC),
Sales[Year]=FORMAT(YEAR(TODAY())-1,”0000″))
)
Now, create a stacked column chart which will give the expected result.
post your comment, if you have any other approaches.