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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kfschaefer
Helper IV
Helper IV

Determine the top item based on Pkg Type.

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.

 

PackageTypeNameItemsTotalQty
BagWhite chocolate moon rocks 250g216
BagNovelty chilli chocolates 250g216
BagChocolate sharks 250g216
BagChocolate frogs 250g192
BagChocolate beetles 250g24
BagNovelty chilli chocolates 500g12
EachBlack and orange fragile despatch tape 48mmx100m360
EachBlack and orange fragile despatch tape 48mmx100m360
EachBlack and orange fragile despatch tape 48mmx75m288
EachClear packaging tape 48mmx75m260
EachBlack and orange fragile despatch tape 48mmx100m252
Each3 kg Courier post bag (White) 300x190x95mm250
EachShipping carton (Brown) 279x254x217mm250
EachShipping carton (Brown) 229x229x229mm250
EachShipping carton (Brown) 305x305x305mm250
EachShipping carton (Brown) 356x229x229mm250
EachShipping carton (Brown) 457x279x279mm250
EachShipping carton (Brown) 500x310x310mm250
8 REPLIES 8
parry2k
Super User
Super User

@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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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])

 

@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. 🙂



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

thanks,

 

Karen

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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