Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
IamTDR
Responsive Resident
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 

ProductJanFebMarAprMayJunJulAugSepOctNovDec
A205214144534645229614187
B556015782033581264928247
C445518162036374364592554
D105416243540641791728237

 

 

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
v-yuta-msft
Community Support
Community Support

Hi IamTDR ,

 

To achieve your requirement, click Query Editor-> Transform-> Cilck on columns [Jan] ~ [Dec]-> Click "Unpivot Columns".

1.PNG 

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)

2.PNG 

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.

3.PNG 

 

Regards,

Jimmy Tao

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

Hi IamTDR ,

 

To achieve your requirement, click Query Editor-> Transform-> Cilck on columns [Jan] ~ [Dec]-> Click "Unpivot Columns".

1.PNG 

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)

2.PNG 

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.

3.PNG 

 

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

IamTDR
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.