Hello!
I have a basket market problem to solve. First, I did my basket market analysis by using this video from Enterprise DNA and it work really well. Then, I found a measure here in PBI Community, to found the top combinations of products and it works too, but I can't use this measure to create a chart.
So what I need is: a ranking of the most sold together product that I can use to create a chart and a table.
All the codes that worked for me is down below:
THE BASKET ANALYSIS
Same Product Selected =
IF( SELECTEDVALUE( '@Cesta_Produtos'[ID] ) <> SELECTEDVALUE( WooCommerce[ID Produto] ),
BLANK(),
TRUE)
Customers Who Bought =
IF([Same Product Selected],
BLANK(),
DISTINCTCOUNT(WooCommerce[CPF ANONIMO NEW]))
Customers w/ both =
VAR MultiPurchaseEvaluation =
CALCULATETABLE(VALUES(WooCommerce[CPF ANONIMO NEW]),
ALL ('SKUs (2)'),
USERELATIONSHIP(WooCommerce[ID Produto], '@Cesta_Produtos'[ID]))
RETURN
IF ([Same Product Selected] = TRUE,
BLANK(),
CALCULATE([Customers Who Bought], MultiPurchaseEvaluation))
TOP 10 COMBINATIONS
Highest Combo =
VAR cj1 =
DISTINCT ( SELECTCOLUMNS (WooCommerce, "Product1", WooCommerce[Nome Curso]))
VAR cj2 =
DISTINCT ( SELECTCOLUMNS ( WooCommerce, "Product2", WooCommerce[Nome Curso] ) )
VAR cj =
FILTER ( CROSSJOIN ( cj1, cj2 ), [Product1] <> [Product2] )
VAR summary =
ADDCOLUMNS (
cj,
"@total",
VAR prod1 = [Product1]
VAR prod2 = [Product2]
RETURN
COUNTROWS (
FILTER (
ALL ( WooCommerce[CPF ANONIMO NEW] ),
AND (
NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( WooCommerce ), WooCommerce[Nome Curso] = prod1 ) ) ),
NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( WooCommerce ), WooCommerce[Nome Curso] = prod2 ) ) )
)
)
)
)
VAR productslist =
CONCATENATEX (
TOPN ( 10, summary, [@total], DESC, [Product1], ASC ),
[Product1] & " e " & [Product2] & UNICHAR(10)
)
RETURN
productslist
Thanks a lot!