Determine the top item based on Pkg Type.

[kfschaefer]

I have data that is grouped by Package type (Each, Bag, etc).  For each pkg type I need to know which item is the most purchased.

How do I get a distinct count per package?  

CountItem = DISTINCTCOUNT(ItemQry[Description])

I have tried to post the powerbi file and I not sure to how to link the file to this post.

PackageTypeName	Items	TotalQty
Bag	White chocolate moon rocks 250g	216
Bag	Novelty chilli chocolates 250g	216
Bag	Chocolate sharks 250g	216
Bag	Chocolate frogs 250g	192
Bag	Chocolate beetles 250g	24
Bag	Novelty chilli chocolates 500g	12
Each	Black and orange fragile despatch tape 48mmx100m	360
Each	Black and orange fragile despatch tape 48mmx100m	360
Each	Black and orange fragile despatch tape 48mmx75m	288
Each	Clear packaging tape 48mmx75m	260
Each	Black and orange fragile despatch tape 48mmx100m	252
Each	3 kg Courier post bag (White) 300x190x95mm	250
Each	Shipping carton (Brown) 279x254x217mm	250
Each	Shipping carton (Brown) 229x229x229mm	250
Each	Shipping carton (Brown) 305x305x305mm	250
Each	Shipping carton (Brown) 356x229x229mm	250
Each	Shipping carton (Brown) 457x279x279mm	250
Each	Shipping carton (Brown) 500x310x310mm	250

[parry2k]

@kfschaefer need bit clarification, how you want the final output looks like? Not sure I uderstood your definiton of most purchased, is it based on qty?

[kfschaefer]

For each Pkg Type I want to display the top number of times the Item(Description) appears.  There should only be 1 Item for each Pkg Type

Pkg       Item                      Top Count

Bag =  White Chocalate     25

Each    Black and orange fragile

          despatch tape 48mmx100m

etc.

latest try:

CountItem = CALCULATE (
    DISTINCTCOUNT (ItemQry[Description]);ItemQry[PackageTypeName])

[parry2k]

@kfschaefer to confirm qty has nothing to do with this, it is based on how many item rows you have under each package type and based on that you want to rank, correct?

Having qty column in your sample model is just confusing and you also have only one item row per package type, so hard to imagine what you are looking for. 🙂

[kfschaefer]

Disregard the qty column and Yes I only want the top count(Item) per each pkg type.  From the entire list I need to determine the top count for each PkgType and only display the top item per Pkg type.

PkgType             Item                           Top Count ofItem

Bag                  White Chocolate          226

Each                Top ABC                        500

Package          123 Parcel                     175

Pair                  JellyBean Shirt               225

[parry2k]

@kfschaefer also what happens if there is tie between two items under same packign type, would you should two at that point.

[kfschaefer]

I forgot to include the Invoice Date - I am working this angle now.

thanks,

Karen

[kfschaefer]

Then we will need to determine if they are different Items and display both other wish if count of item >2 remove 1.