cancel
Showing results for
Did you mean:
Highlighted
Helper II

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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

2 REPLIES 2
Highlighted
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

Highlighted
Helper II

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors