Cross Selling - Pivot Table

[danimarc12]

Hello everyone!

I want to display on a pivot table the association between 2 brands in the sales orders. My expected result is something like this:

I splitted the field "brand" in two fields, "brand" and "brand2" to use both in the pivot table.
I've tried tu use this measure:

count(DISTINCT scontrino) / count(total scontrino)

where scontrino is the sales order but the result is this one:

For sure I'm doing wrong. I have no knlowledge of statistic.

Could anyone help me to solve my issue?

Thanks a lot!

[iliyansomlev]

Hello, Danimarc12,

In the way you want to calculate it, you will not get correlation, but rather relative weight (in terms of number of orders) of all brand combinations.

You get the shown result, because your brand and brand2 values are columns in the same table - most probably Sales Order table, and I guess that you have SalesOrderID in this table. If so, instead of loading your brand field again (as brand2) in the same table, try loading it in a new table, that is linked to your Sales Order table via the SalesOrderID keyfield:

NewTable: LOAD SalesOrderID, brand as brand2 resident SalesOrders;

Then you will get in your pivot the relative weights of all combinations.

Hope this helps,

Iliyan

[danimarc12]

Hello @iliyansomlev , thanks a lot for your answer.

I did as you said, creating a new table:

where scontrino is the order and marca2 is the brand2 field.

The result is the following:

It's better than before but I'm afraid if my measure is not correct.

I'm using the same measure:
count(DISTINCT scontrino) / count(TOTAL scontrino)

Also, it should be a value which is not changing by selecting other filters? Because now, if I select any filter, these values % are changing.

Thank a lot 🙏

[danimarc12]

This red one (it's the same brand in the row and column) should be 100%, correct?

[iliyansomlev]

Hi again,

You get the weight of brands being sold compared to all sales orders. So if you have '-' in some cell this means those two brands were never sold together. The same brand in row and column will not give 100%, because it is also a weight compared to all sales orders - it shows the frequency of this brand being sold.

The weights will be responsive to your selections. If you do not want this to happen, you can use {1} in set analysis or load the data as an island in the model (LOAD scontrino as scontrino2, brand as brand1 Resident Orders and LOAD scontrino as scontrino2, brand as brand2 Resident Orders ) - 2 new brands columns linked via scontrino2 field but unlinked from the rest of your model.

Best regards,

Iliyan