Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |