cancel
Showing results for
Did you mean:
Highlighted
Helper I

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 I

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!

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors