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

[Modriscoll]

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!

[stevedep · Accepted solution]

Hi @Modriscoll ,

Yes, this is possible, see image below:

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