cancel
Showing results for
Did you mean:
Resolver III

## 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

Community Champion

Hi @IamTDR

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

Thanks

Raj

Resolver III

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.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

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!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors