Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |