Tag Archives: TOPN

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.

1. If we use a simple bar/column chart, then we will get multiple standalone bars or columns.
2. 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

1. Create a DAX table with below logic
   1. TOP 5
   2. Filter – Current Year & Previous Year
   3. 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.