Forum Discussion
Product Pathways
Good Morning Team,
Can someone please assist me?, I am looking for a way to show the pathways my customers are using to get a product.
Below Table 1 is a example of my data type, a list of customer ID's, date of order and product bought.
Table 1:
| Cst | Date | Product |
| A | 2022/01/01 | Kettle |
| A | 2022/01/02 | Hairdryer |
| B | 2022/01/01 | Kettle |
| C | 2022/01/02 | Toaster |
| A | 2022/01/03 | Kettle |
| D | 2022/01/04 | Toaster |
| C | 2022/01/04 | Kettle |
| B | 2022/01/05 | Hairdryer |
| D | 2022/01/05 | Kettle |
Solution example:
I want to show the pathways my customers used to buy their lastest kettle. So looking only at their last kettle bought,
| Pathways to Kettle | Cst C & D bought a totaster before their last kettle | 2 | toaster -> kettle |
| Cst A bought a hairdryer before their last kettle | 1 | hairdryer -> kettle | |
| Cst B bought a kettle first | 1 | kettle first |
Is something like this possible in Powerbi?
Thank you in advance
Sam
Hi SammyNed ,
I have created a simple sample, please refer to it to see if it helps you.
Create a column.
rankx = RANKX(FILTER('Table','Table'[Cst]=EARLIER('Table'[Cst])),'Table'[Date],,ASC,Dense)Then create a measure.
Kettle = VAR _1 = CONTAINSSTRINGEXACT ( CONCATENATEX ( ALLSELECTED ( 'Table' ), 'Table'[Product], "" ), "Kettle" ) = TRUE () VAR _1first = CALCULATE ( MAX ( 'Table'[Product] ), FILTER ( ALL ( 'Table' ), 'Table'[Cst] = SELECTEDVALUE ( 'Table'[Cst] ) && 'Table'[rankx] = 1 ) ) VAR _2first = CALCULATE ( MAX ( 'Table'[Product] ), FILTER ( ALL ( 'Table' ), 'Table'[Cst] = SELECTEDVALUE ( 'Table'[Cst] ) && 'Table'[rankx] = 2 ) ) VAR _3first = CALCULATE ( MAX ( 'Table'[Product] ), FILTER ( ALL ( 'Table' ), 'Table'[Cst] = SELECTEDVALUE ( 'Table'[Cst] ) && 'Table'[rankx] = 3 ) ) RETURN _1first & " " & _2first & " " & _3firstIf I have misunderstood your meaning, please provide more details with your desired output.
Best Regards
Community Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi tamerj1
There are 5 products in total, however over 40k customers. I basically want to find some sort of corelation between the last product the customer bought and what they bought before that. So do more customers buy toasters and then kettles or do more customers buy haridryers first when they come on site for example.
Hi SammyNed ,
I have created a simple sample, please refer to it to see if it helps you.
Create a column.
rankx = RANKX(FILTER('Table','Table'[Cst]=EARLIER('Table'[Cst])),'Table'[Date],,ASC,Dense)Then create a measure.
Kettle = VAR _1 = CONTAINSSTRINGEXACT ( CONCATENATEX ( ALLSELECTED ( 'Table' ), 'Table'[Product], "" ), "Kettle" ) = TRUE () VAR _1first = CALCULATE ( MAX ( 'Table'[Product] ), FILTER ( ALL ( 'Table' ), 'Table'[Cst] = SELECTEDVALUE ( 'Table'[Cst] ) && 'Table'[rankx] = 1 ) ) VAR _2first = CALCULATE ( MAX ( 'Table'[Product] ), FILTER ( ALL ( 'Table' ), 'Table'[Cst] = SELECTEDVALUE ( 'Table'[Cst] ) && 'Table'[rankx] = 2 ) ) VAR _3first = CALCULATE ( MAX ( 'Table'[Product] ), FILTER ( ALL ( 'Table' ), 'Table'[Cst] = SELECTEDVALUE ( 'Table'[Cst] ) && 'Table'[rankx] = 3 ) ) RETURN _1first & " " & _2first & " " & _3firstIf I have misunderstood your meaning, please provide more details with your desired output.
Best Regards
Community Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
