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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
newbie49
New Member

Help designing a matrix to identify market gaps

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:

IDNameRank
1Okely Dokely Store2
2Storesamazing1
3Store of Meh3
   

 

And Sales:

SKUQtyStore IDRep
1234

1

Molly

78922Judy
12342Judy

 

I want to create a table like so:

 

NameRank123789# Products
Storesamazing1XX1
Okely Dokely Store2X 1
Store of Meh3  0
# of stores 21 

 

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 
1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1673403747663.png

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:

vyueyunzhmsft_1-1673403787080.png

(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 .

vyueyunzhmsft_2-1673403880792.png

 

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

 

View solution in original post

1 REPLY 1
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1673403747663.png

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:

vyueyunzhmsft_1-1673403787080.png

(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 .

vyueyunzhmsft_2-1673403880792.png

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.