Forum Discussion
Solved
Calculating sales associate between different categories.
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...
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 tbl23. create a columnColumn =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
ryan_mayu
Super User
Super User
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