Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
Hi
In Power BI, can you write a measure that could display a unique product's top 3 selling months over a time span?
Ideally I would like to show each product's top selling months.
Example data
Product | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
A | 20 | 52 | 14 | 14 | 45 | 34 | 64 | 52 | 29 | 61 | 41 | 87 |
B | 55 | 60 | 15 | 78 | 20 | 33 | 58 | 12 | 64 | 92 | 82 | 47 |
C | 44 | 55 | 18 | 16 | 20 | 36 | 37 | 43 | 64 | 59 | 25 | 54 |
D | 10 | 54 | 16 | 24 | 35 | 40 | 64 | 17 | 91 | 72 | 82 | 37 |
So for example, Product A results should be Dec at 87, Jul at 64, and Oct at 61.
I could do this fairly easily in excel using conditional formatting top/bottom rules. Trying to translate this to BI
THanks!
Solved! Go to Solution.
Hi IamTDR ,
To achieve your requirement, click Query Editor-> Transform-> Cilck on columns [Jan] ~ [Dec]-> Click "Unpivot Columns".
After apply&closed, create a calculate column using DAX formula as below:
Rank = RANKX(FILTER(Table1, Table1[Product] = EARLIER(Table1[Product])), RANKX(ALL(Table1), Table1[Value]), , ASC, Dense)
Finally, create a matrix chart and a slicer chart based on Rank column, set range 1~3 in the slicer chart, you will see what you expected.
Regards,
Jimmy Tao
Hi IamTDR ,
To achieve your requirement, click Query Editor-> Transform-> Cilck on columns [Jan] ~ [Dec]-> Click "Unpivot Columns".
After apply&closed, create a calculate column using DAX formula as below:
Rank = RANKX(FILTER(Table1, Table1[Product] = EARLIER(Table1[Product])), RANKX(ALL(Table1), Table1[Value]), , ASC, Dense)
Finally, create a matrix chart and a slicer chart based on Rank column, set range 1~3 in the slicer chart, you will see what you expected.
Regards,
Jimmy Tao
Hi @IamTDR
Pivot the data in Query Editor and then you can use the TOP N/ Bottom N filter in visual filter.
Thanks
Raj
Thanks for the quick reply.
This method would provide top sales overall though correct? I am interested in keep each unique product and then being Power BI show me the top sales months over a few years of data.
Check out the May 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
72 | |
71 | |
70 | |
51 | |
47 |
User | Count |
---|---|
45 | |
38 | |
33 | |
30 | |
28 |