Calculating sales associate between different categories.

[dream36876]

I would like to analyze the association of sales between different categories.

For example:
In a single order, how many orders that include facial cleansers also include scalp cleansers?

The right side form is ideal result that show the number of orders for each category that include items from other categories purchased together.

[ryan_mayu · Accepted solution]

@dream36876 

1. create a column in order table

productname = RELATED(Dim_Product[Name])

'

2.  create a table

Table =

VAR tbl=FILTER(ADDCOLUMNS(CROSSJOIN(DISTINCT(Dim_Product[Name]),SELECTCOLUMNS(DISTINCT(Dim_Product[Name]),"name2",[Name])),"check",if([Name]=[name2],1,0)),[check]=0)

VAR tbl2=SELECTCOLUMNS(tbl,"Name",[Name],"Co-Buy",[name2])

return tbl2

3. create a column

Column =

VAR ta=CALCULATETABLE(distinct('Order'[OrderId]),filter('Order','Order'[productname]='Table'[Name]))

VAR tb=CALCULATETABLE(distinct('Order'[OrderId]),filter('Order','Order'[productname]='Table'[Co-Buy]))

VAR tc=INTERSECT(ta,tb)

return COUNTROWS(tc)

pls see the attachment below