Help designing a matrix to identify market gaps

[newbie49]

Hello!

I'm a total newb to Power BI. I'm porting over a bunch of reporting that I designed on a legacy and I'm trying to replicate functionality. For simplicty sake, let's say I have 2 tables:

Locations:

ID	Name	Rank
1	Okely Dokely Store	2
2	Storesamazing	1
3	Store of Meh	3

And Sales:

SKU	Qty	Store ID	Rep
123	4	1	Molly
789	2	2	Judy
123	4	2	Judy

I want to create a table like so:

Name	Rank	123	789	# Products
Storesamazing	1	X	X	1
Okely Dokely Store	2	X		1
Store of Meh	3			0
# of stores		2	1

So far the best solution I've come up with on my own is a Matrix Visualization with rows containing Location.Name, columns containing Sales.Sku, and Values is a Measure as follows: 

StoreCarriesProduct = if(COUNTROWS(Sales)>0,"⦿","◦")

It's pretty close, but there are several problems I can't seem to solve:

1) how can I have a subtotal on each row that shows the number of products in that particular store?

2) how can I have a subtotal on each column that shows the number of stores that product is in?

3) how can add the rank column that is sortable? (This is just a column out of the db... it's not calculated)

4) since I'm adding the location names from the locations table with no concept of Rep when I try to filter by Rep it successfully removes the sales data (the dots go away in the visual), but the stores that don't belong to the Rep remain. How can I solve that?

Thank you in advance for your help! I'm super stoked to learn more about this platform!

Cheers!

Edit: Here's the basic Matrix I currently have along with the sample data in a pbix: https://drive.google.com/file/d/1Xjv37mknB_ROKUpzE5uBwOhWpICVOrGv/view?usp=sharing 

[v-yueyunzh-msft · Accepted solution]

Hi , @newbie49 

According to your description, you want to make a custom Matrix visual .

Here are the steps you can refer to .

(1)For your need , we need to create the column headers and the row headers starting from scratch.

I do it in Power Query Editor. You can create two blank query:

And then enter this:

= Table.AddIndexColumn(Table.FromColumns({List.Distinct(Locations[Name]) & {"# of stores"}}), "Index", 1, 1, Int64.Type)

= Table.AddIndexColumn(Table.FromColumns({{"Rank"} &List.Distinct(Sales[SKU]) & {"# Products"}}), "Index", 1, 1, Int64.Type)

Then we can create two dimension tables:

(2)Then we can apply the data to Desktop and we can create a measure :

Measure = var _columnName = MAX('Column Headers'[Column1])
var _rowName= MAX('Row Headers'[Column1])
var _rank_value = CALCULATE( MAX('Locations'[Rank]) , TREATAS( {_rowName} , 'Locations'[Name]))
var _id =MAXX( FILTER('Locations' , 'Locations'[Name] = _rowName) , [ID])
var _products =COUNTROWS( DISTINCT(SELECTCOLUMNS( FILTER('Sales' , 'Sales'[Store ID] = _id) , "Rep" , [Rep])))
var _sku =IFERROR( VALUE(_columnName) ,BLANK())
var _iscontains =COUNTROWS( FILTER( 'Sales' , 'Sales'[Store ID] = _id && 'Sales'[SKU] =_sku) )
var _flag =  IF( _iscontains >=1 ,"X",BLANK())
var _stores = COUNTROWS( FILTER( 'Sales' , 'Sales'[SKU] =_sku) )
return
IF(_columnName = "Rank" , _rank_value , IF(_columnName="# Products" ,_products , IF(_rowName="# of stores" ,_stores,  _flag  )))

(3)Then we can put the fields we need and the measure on the Matrix visual and we can meet your need:
And we can also configure the [Column1] by [Index] Column1 and so on .

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly