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.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
123 | |
74 | |
66 | |
53 | |
53 |
User | Count |
---|---|
199 | |
104 | |
88 | |
79 | |
77 |