Correlation in Product Structure

[Anonymous]

Hello there,

I'm working on a model where I could look at the similarity between products through the product structure.

The product structure looks like this:

TopProduct	ComponentPart
1234	ABC1
1234	BCD2
1234	CDE3
1234A	ABC1
1234A	EFG1

I would like to plot out a correlation matrix, like this

TopProduct	1234	1234A
1234	1	0,5
1234A	0,33	1

The answer in the correlation would be how many products in that part was also in the other part. --> Product 1234A had 1 similar product component as product 1234 had --> 1/3 

I hope I made the question clear. Anybody have ideas on how to do this kind of calculation?

Thanks in advance!

[Greg_Deckler · Accepted solution]

@Anonymous - I did it like this:

Measure 2 = 
    VAR __T = MAX('Table (4)'[TopProduct])
    VAR __T1 = MAX('Table 2'[TopProduct])
    VAR __Parts = SELECTCOLUMNS(FILTER(ALL('Table (4)'),[TopProduct] = __T),"__Parts",[ComponentPart])
    VAR __Parts1 = SELECTCOLUMNS(FILTER(ALL('Table (4)'),[TopProduct] = __T1),"__Parts",[ComponentPart])
    VAR __In = INTERSECT(__Parts,__Parts1)
RETURN
    COUNTROWS(__In) / COUNTROWS(__Parts)

PBIX is attached below sig, you want Table (4) and Table 2.

[Anonymous]

Amazing! Thank you!

Need to understand the dax-code behind it still, but that did the trick! Thanks alot!

[Greg_Deckler]

@Anonymous - Sure, the first two lines simply figure out where we are in the matrix getting the value in the row and the column. We then create two table variables that filter out the rows in our base fact table based upon the values we have for our row and column. We use SELECTCOLUMNS to just get the column we are interested in.

INTERSECT is the key, it returns the rows that are common between the first table and the last table. So then it is just dividing this by our count of rows.

[amitchandak]

@Anonymous , refer if these can help

https://www.mssqltips.com/sqlservertip/5197/correlation-analysis-using-correlation-plot-in-power-bi-desktop/

https://www.youtube.com/watch?v=xbTNYoW116g