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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.