Top 3 unique products sold per product category on which date

[parry2k]

Hi there,

I have a table with following fields:

Product Category

Product

Sale Date

Amount

I'm looking top 3 unique product sold for each product category and on which date.

Any help will be much appreciated.

Thanks,

P

[v-yuezhe-msft · Accepted solution]

@parry2k,

Firstly, create a new table using DAX below.

SummaryTable = SUMMARIZE('top 3 product sample data','top 3 product sample data'[Customer],'top 3 product sample data'[Product],"totalamount",SUM('top 3 product sample data'[ Amount ]))

Secondly, create a new column using DAX below in the new table.
Column = RANKX(FILTER('SummaryTable','SummaryTable'[Customer]=EARLIER('SummaryTable'[Customer])),'SummaryTable'[totalamount],,DESC,Dense)

Thirdly, create a Matrix as shown in the following screenshot, drag the new column to Visual level filters and set its value to be less than or equal to 3.

Regards,
Lydia

[parry2k]

Hi @v-yuezhe-msft

Going to try it in next hour or so and let you guys know how it goes? Stay tuned.

Thanks,

P

[parry2k]

Hi @v-yuezhe-msft

One thing you missed in your proposed solution to include date, we don't want to see top 3 product across all time but instead top 3 product for specific date for a customer whereas your solution is giving top 3 product for each customer not considering date, I think two changes are required to achive that,

1st include date in summary

2nd modify rankx function

Thanks,

P

[parry2k]

Hey @v-yuezhe-msft

I extended your solution further, here is what I did, I highlighted the changes in BOLD 

SummaryTable = SUMMARIZE(
'top 3 product sample data',
'top 3 product sample data'[Customer],
'top 3 product sample data'[Product],
'top 3 product sample data'[Date],
"totalamount",SUM('top 3 product sample data'[ Amount ])
)

Extended you rank column to add product to it, so now I get rank for each product for that customer.

Rank = RANKX(
FILTER(
'SummaryTable','SummaryTable'[Customer]=EARLIER('SummaryTable'[Customer]) && 
'SummaryTable'[Product]=EARLIER('SummaryTable'[Product])
),
'SummaryTable'[totalamount],,DESC,Dense
)

Added new column for Rank 2

Rank 2 = RANKX(
FILTER(
'SummaryTable','SummaryTable'[Customer]=EARLIER('SummaryTable'[Customer]) && 
'SummaryTable'[Column]=1)
),
'SummaryTable'[totalamount],,DESC,Dense
)

In filter I did 

Rank = 1

Rank 2 Less Than Equal to 3

This gave me the result I'm looking for. Thanks for your help.

P

[Ashish_Mathur]

Hi,

Show your data and the expected result.

[parry2k]

here is link to sample data and also in csv remarks columns has what product/date/amount is 1st/2nd/3rd for each customer.

https://drive.google.com/file/d/0B4dPgH9_BPBrbG01OFg2cl83R0k/view?usp=sharing

Thanks,

P

[Ashish_Mathur]

Hi,

Why would you want to return the result of 1,2,3 in a seperate column.  I think you can get yoru desired result via a Pivot Table.  Take a look at the screenshot

[parry2k]

I didn't mean to return restult in seperate column, tht was my comment what is expected result.

Although I'm not planning to do it in PowerPivot but in PowerBI.