This article covers a real-time scenario which was asked by one of the customers.
Show the sales of the top 5 countries for the current year and previous year.
The above requirement seems to be simple, but we don’t have the same customers on the previous and current year.
Follow the below 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.