cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Return top product by sales for each producer

Hello

 

I'm trying to create a table/matrix visual that will display the name of a producer and the top product they've sold based on the total number of sales. I've included a sample dataset to give you an idea of what I've got and the expected output.

 

Thanks. 

 

TABLE
ProducerProductSales
Producer 1Product 110
Producer 1Product 220
Producer 1Product 130
Producer 1Product 340
Producer 2Product 150
Producer 2Product 260
Producer 2Product 370
Producer 3Product 180
Producer 3Product 290
Producer 3Product 2100
Producer 3Product 2110
Producer 3Product 3120
   
   
   
Desired Output
ProducerProduct 
Producer 1Product 1 
Producer 2Product 3 
Producer 3Product 2 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Hi @DJBAJG 

Try this:

1. Place Table1[Producer] in the rows of a visual table

2. Place this measure in the visual:

TopSeller =
VAR AuxTable_ =
    CALCULATETABLE (
        DISTINCT ( Table1[Product] );
        TOPN ( 1; Table1; CALCULATE ( SUM ( Table1[Sales] ); ALL ( Table1[Sales] ) ) )
    )
RETURN
    CONCATENATEX ( AuxTable_; [Product]; ", " )

  

Please mark the question solved when done and consider giving kudos if posts are helpful.

 

Cheers  Datanaut

View solution in original post

2 REPLIES 2
Highlighted
Super User II
Super User II

Hi @DJBAJG 

Try this:

1. Place Table1[Producer] in the rows of a visual table

2. Place this measure in the visual:

TopSeller =
VAR AuxTable_ =
    CALCULATETABLE (
        DISTINCT ( Table1[Product] );
        TOPN ( 1; Table1; CALCULATE ( SUM ( Table1[Sales] ); ALL ( Table1[Sales] ) ) )
    )
RETURN
    CONCATENATEX ( AuxTable_; [Product]; ", " )

  

Please mark the question solved when done and consider giving kudos if posts are helpful.

 

Cheers  Datanaut

View solution in original post

Highlighted

Hi AIB

 

Thanks for the quick response. That seems to get me what I needed with the addition of a RETURN FIRSTNONBLANK to eliminate multiple products being returned.

 

Thanks again.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors