DAX – Top N for each Previous & Current Year

ByHariharan Rajendran

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.

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer with 9+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Leave a Reply