cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Responsive Resident

## Unique Products Top 3 Sales Months

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!

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

Anonymous
Not applicable

Hi @IamTDR

Pivot the data in Query Editor and then you can use the TOP N/ Bottom N filter in visual filter.

Thanks

Raj

Responsive Resident

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.