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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Most sold product

Hello,

 

I am trying to do some customer analysis with a transactional data set.

 

I would like the ability to draw from all of the individual transaction lines a table somethin like this:

 

Customer Name | Most bought product

 

The way I would define the Most bought product column is the product with the highest sales value.

 

For example:

 

If Joe Blogs bought the following off me:

 

£100 on Carrots

£50 on potatoes 

£75 on Steak

£150 on Lobster

 

I would want the table to show 

 

Joe Bloggs | Lobster

 

Thank you for your help

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

 

I would use the DAX function TOPN: https://dax.guide/topn/ for example this measure:

TOP 1 Product = 
CONCATENATEX(
    TOPN(
        1 
        , VALUES('Product'[ModelName]) 
        , CALCULATE(SUM('Reseller Sales'[OrderQuantity])) 
    )
    , [ModelName]
    , ", "
)

Please be aware that, there are multiple products, that have been the same "value", that you are using for the comparison.

 

Hopefully this provides some ideas, to slove your requirement.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
Cmcmahan
Resident Rockstar
Resident Rockstar

What do you want to do in the case of a tie? 

 

MostPurchasedProduct = CALCULATE( SELECTEDVALUE(Products[ItemName]), TOPN(1, ADDCOLUMNS( VALUES(Products[ItemName]), "Total Spent", CALCULATE(SUM(Products[Amt Spent]))), [Total Spent], DESC))
TomMartens
Super User
Super User

Hey,

 

I would use the DAX function TOPN: https://dax.guide/topn/ for example this measure:

TOP 1 Product = 
CONCATENATEX(
    TOPN(
        1 
        , VALUES('Product'[ModelName]) 
        , CALCULATE(SUM('Reseller Sales'[OrderQuantity])) 
    )
    , [ModelName]
    , ", "
)

Please be aware that, there are multiple products, that have been the same "value", that you are using for the comparison.

 

Hopefully this provides some ideas, to slove your requirement.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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