Forum Discussion
Advocate IHelp creating Channel opt in pivot / crosstab table
Hi
I'm not sure how to word my question but this is the kind of table that I want to produce (without the totals is fine, just added them in to illustrate the data):
Where the numbers are customer counts. So the above mock up is 1,000 customers of which 800 are email opt in, 600 are mail opt in and 250 are phone opt in. The chart shows this plus the crossover between any 2 channels.
I'm not sure how to model the data to support this visual / what vis to select / what measure to create!
My source data is currently in the below summary format but of course I am happy to remodel if needed:
Customer ID, is Email, is Mail, is Phone
C001,1,0,0
C002,1,1,0
C003,0,1,1
C004,0,0,1
etc
I'm also open to rethinking how to show this crossover information. Like if I can set up something to let my users select which channels they want to include and show all combinations and maybe add in a heirarchy order that would be amazing. But I thought I'd first try a simple cross tab and got stuck 😛
Thanks for any pointers. I'm currently rudderless with this one!
Hi klev28
I would recommend the "basket analysis" pattern for this.
See here for an explanation:
https://www.daxpatterns.com/basket-analysis/
For this pattern to work, you should unpivot the columns "is Email", "is Mail", "is Phone" so that you end up with this structure:
Customer Channel C001 Email C002 Email C002 Mail C003 Mail C003 Phone C004 Phone ... ... Then, if you follow the article, you should create two copies of a Channel dimension.
The # Orders Both measure from the article is similar to the one you're displaying in your example. To produce a matrix like your example, you would place one copy of Channel on rows and the other copy on columns.
Please post back if you need more help on the details.
Regards
Hi klev28 ,
I've made a test for your reference:
1\I assume there is a table(Table)
2\Add a caculate table
Channels = DATATABLE ( "Channel", STRING, { { "Email"}, { "Mail"}, { "Phone"}, { "Total" } } )3\Add 4 measures for the caculate table 'Channels'
Email = Switch( SELECTEDVALUE(Channels[Channel]), "Email",COUNTROWS(FILTER('Table','Table'[is Email]=1)), "Mail",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Mail]=1 )), "Phone",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Phone]=1 )), "Total",COUNTROWS(FILTER('Table','Table'[is Email]=1)) )Mail = Switch( SELECTEDVALUE(Channels[Channel]), "Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Mail]=1)), "Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1 )), "Phone",COUNTROWS(FILTER('Table','Table'[is Mail]=1 && 'Table'[is Phone]=1 )), "Total",COUNTROWS(FILTER('Table','Table'[is Mail]=1)) )Phone = Switch( SELECTEDVALUE(Channels[Channel]), "Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Phone]=1)), "Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1 && 'Table'[is Phone]=1)), "Phone",COUNTROWS(FILTER('Table','Table'[is Phone]=1 )), "Total",COUNTROWS(FILTER('Table','Table'[is Phone]=1)) )Total = Switch( SELECTEDVALUE(Channels[Channel]), "Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 )), "Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1)), "Phone",COUNTROWS(FILTER('Table','Table'[is Phone]=1 )), "Total",COUNTROWS('Table') )4\Add a table visual
Best Regards,
Bof
4 Replies
Hi klev28 ,
I've made a test for your reference:
1\I assume there is a table(Table)
2\Add a caculate table
Channels = DATATABLE ( "Channel", STRING, { { "Email"}, { "Mail"}, { "Phone"}, { "Total" } } )3\Add 4 measures for the caculate table 'Channels'
Email = Switch( SELECTEDVALUE(Channels[Channel]), "Email",COUNTROWS(FILTER('Table','Table'[is Email]=1)), "Mail",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Mail]=1 )), "Phone",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Phone]=1 )), "Total",COUNTROWS(FILTER('Table','Table'[is Email]=1)) )Mail = Switch( SELECTEDVALUE(Channels[Channel]), "Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Mail]=1)), "Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1 )), "Phone",COUNTROWS(FILTER('Table','Table'[is Mail]=1 && 'Table'[is Phone]=1 )), "Total",COUNTROWS(FILTER('Table','Table'[is Mail]=1)) )Phone = Switch( SELECTEDVALUE(Channels[Channel]), "Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 && 'Table'[is Phone]=1)), "Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1 && 'Table'[is Phone]=1)), "Phone",COUNTROWS(FILTER('Table','Table'[is Phone]=1 )), "Total",COUNTROWS(FILTER('Table','Table'[is Phone]=1)) )Total = Switch( SELECTEDVALUE(Channels[Channel]), "Email",COUNTROWS(FILTER('Table','Table'[is Email]=1 )), "Mail",COUNTROWS(FILTER('Table','Table'[is Mail]=1)), "Phone",COUNTROWS(FILTER('Table','Table'[is Phone]=1 )), "Total",COUNTROWS('Table') )4\Add a table visual
Best Regards,
Bof
klev28Hi Bof
This is a really nice neat solution and I like that it will scale fairly easily as I actually have a 4th channel to add in (SMS). Thank you so much for taking the time to model it out for me!
I've sucessfully replicated this and it is both perfect and easy to understand 🙂
Thanks so much, Kezl
- OwenAuger
Super User
Hi klev28
I would recommend the "basket analysis" pattern for this.
See here for an explanation:
https://www.daxpatterns.com/basket-analysis/
For this pattern to work, you should unpivot the columns "is Email", "is Mail", "is Phone" so that you end up with this structure:
Customer Channel C001 Email C002 Email C002 Mail C003 Mail C003 Phone C004 Phone ... ... Then, if you follow the article, you should create two copies of a Channel dimension.
The # Orders Both measure from the article is similar to the one you're displaying in your example. To produce a matrix like your example, you would place one copy of Channel on rows and the other copy on columns.
Please post back if you need more help on the details.
Regards
- klev28
Hi Owen
Thanks for this. I've used the other solution for my Channel crosstab but the basket analysis that you highlighted is also very relevant and I will almost certainly be using this for the product page. It will save having to create a page per product group - which was also on my list of solutions to find.
I'm seriously delighted with both solutions 😄
Kezl