Unique Products Top 3 Sales Months

[IamTDR]

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!

[v-yuta-msft · Accepted 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

[rajendran]

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]

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.