Help creating Channel opt in pivot / crosstab table

[klev28]

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!

[OwenAuger · Accepted solution]

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

[v-bofeng-msft · Accepted solution]

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

[klev28]

Hi 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 · Accepted solution]

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