Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Modriscoll
New Member

Matrix visual to show top 5 vendors and their top 5 products

4:59 PM

I am a new user of Power BI and am hitting roadblock with one of the views I'm trying to present. I am trying to show my top 5 vendors and listed under their name would be their top 5 products. Here is what I'm trying to show.

 

 

Vendor 1

Vendor 2

Vendor 3

Vendor 4

Vendor 5

total sales(units)

300

250

250

200

250

Product 1

widgets

tires

bottles

sneakers

bottles

Product 2

cans

bottles

widgets

tires

widgets

Product 3

sneakers

widgets

cans

bottles

cans

Product 4

tires

cans

sneakers

widgets

sneakers

Product 5

bottles

sneakers

tires

cans

tires

 

I've tried to use the Matrix visual and succeeded in getting the products listed in rows with their count. But it seems that when I try to show a second layer of top 5 (products by product name) name  Power BI won't let me due to only 1 "N" sort on the visual.

Looking for some guidance as to how to achieve this view.  Different Visual? DAX formula? Something else? Impossible?

Thanks and I appreciate the help!

1 ACCEPTED SOLUTION
stevedep
Memorable Member
Memorable Member

Hi @Modriscoll ,

 

Yes, this is possible, see image below:

stevedep_0-1614016547325.png

The trick is to create a (more or less) separate table with the lines for the topn products. Since we want to show it in a matrix we do need to join the table in the data model, therefore, I added this joiner column in both tables (containing 1). 

The other part is to calculate the rank per product (which is filtered by the vendor). The productrank table (in the rows of the matrix) has a certain value (selectedvalue), this is used to retreive the product with that rank. 

 

The file is attached.

Kind regards, Stev.e 

 

View solution in original post

1 REPLY 1
stevedep
Memorable Member
Memorable Member

Hi @Modriscoll ,

 

Yes, this is possible, see image below:

stevedep_0-1614016547325.png

The trick is to create a (more or less) separate table with the lines for the topn products. Since we want to show it in a matrix we do need to join the table in the data model, therefore, I added this joiner column in both tables (containing 1). 

The other part is to calculate the rank per product (which is filtered by the vendor). The productrank table (in the rows of the matrix) has a certain value (selectedvalue), this is used to retreive the product with that rank. 

 

The file is attached.

Kind regards, Stev.e 

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.