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 right side form is ideal result that show the number of orders for each category that include items from other categories purchased together.
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
1 Reply
ryan_mayuSuper 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 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