Return top product by sales for each producer

[DJBAJG]

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
Producer	Product	Sales
Producer 1	Product 1	10
Producer 1	Product 2	20
Producer 1	Product 1	30
Producer 1	Product 3	40
Producer 2	Product 1	50
Producer 2	Product 2	60
Producer 2	Product 3	70
Producer 3	Product 1	80
Producer 3	Product 2	90
Producer 3	Product 2	100
Producer 3	Product 2	110
Producer 3	Product 3	120
Desired Output
Producer	Product
Producer 1	Product 1
Producer 2	Product 3
Producer 3	Product 2

[AlB · Accepted solution]

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  

[DJBAJG]

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.