Super quick one:) Count of customers who have been seen by more than one channel

[junglejimsvet]

Hi super people,

I'm sure its simple but can't get my head around it:).  I simply want a measure to count how many customers have been seen by more than one channel (so from the example below, 2... Smith and Jones).  Quick pointer would be very appreciated:)

Customer	Call ID	Seen through which channel
Mr Smith	1	Virtual
Mr Rogers	2	Email
Mr Jones	3	Virtual
Mr Smith	4	Face to Face
Mr Jones	5	Face to Face
Mr Rogers	6	Email
Mr Edwards	7	Congress
Need Count of Customers seen though more than one channel

The overall goal is to have % with numerator of customers seen by more than one channel and denominator of customers seen.

Thanks in advance for your help:)

Jim

[OwenAuger · Accepted solution]

Hi @junglejimsvet 

To calculate this, you have to take a table of distinct customers, filter that table to those with more than 1 channel, and count the rows of the resulting table.

There are multiple ways to do this, and here are a couple. V1 should perform better in general, but V2 is shorter code.

V1
(Similar structure to measure in this article)

Customers Seen By More Than One Channel V1 = 
VAR CustomerChannel =
    SUMMARIZE (
        Calls,
        Calls[Customer],
        Calls[Channel]
    )
VAR CustomerNumChannel =
    GROUPBY (
        CustomerChannel,
        'Calls'[Customer],
        "@NumChannel",
        SUMX ( CURRENTGROUP (), 1 )
    )
VAR CustomerMoreThanOneChannel =
    FILTER (
        CustomerNumChannel,
        [@NumChannel] > 1
    )
RETURN
    COUNTROWS ( CustomerMoreThanOneChannel )

Customers Seen By More Than One Channel V2 = 
COUNTROWS (
    FILTER (
        VALUES ( Calls[Customer] ),
        CALCULATE ( DISTINCTCOUNT ( Calls[Channel] ) ) > 1
    )
)

The measure would need to be changed if you intend to use it in a visual including Channel, and you want the measure to look outside the currently filtered Channel.

Small example PBIX attached.

Regards,

Owen

[junglejimsvet]

Hi both, thanks so much for this - perfect.  As ever I am always in awe of your guys skills and prearedness to help others

Many thanks

Jim

[AlB · Accepted solution]

Hi @junglejimsvet 

Try this measure:

Measure = 
VAR custSeen_ = DISTINCTCOUNT ( Table1[Customer] )
VAR custSeenMore1Chan_ =
    COUNTROWS (
        FILTER (
            DISTINCT ( Table1[Customer] ),
            CALCULATE ( DISTINCTCOUNT ( Table1[Seen through which channel] ) ) > 1
        )
    )
RETURN
    DIVIDE ( custSeenMore1Chan_, custSeen_ )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[OwenAuger · Accepted solution]

Hi @junglejimsvet 

To calculate this, you have to take a table of distinct customers, filter that table to those with more than 1 channel, and count the rows of the resulting table.

There are multiple ways to do this, and here are a couple. V1 should perform better in general, but V2 is shorter code.

V1
(Similar structure to measure in this article)

Customers Seen By More Than One Channel V1 = 
VAR CustomerChannel =
    SUMMARIZE (
        Calls,
        Calls[Customer],
        Calls[Channel]
    )
VAR CustomerNumChannel =
    GROUPBY (
        CustomerChannel,
        'Calls'[Customer],
        "@NumChannel",
        SUMX ( CURRENTGROUP (), 1 )
    )
VAR CustomerMoreThanOneChannel =
    FILTER (
        CustomerNumChannel,
        [@NumChannel] > 1
    )
RETURN
    COUNTROWS ( CustomerMoreThanOneChannel )

Customers Seen By More Than One Channel V2 = 
COUNTROWS (
    FILTER (
        VALUES ( Calls[Customer] ),
        CALCULATE ( DISTINCTCOUNT ( Calls[Channel] ) ) > 1
    )
)

The measure would need to be changed if you intend to use it in a visual including Channel, and you want the measure to look outside the currently filtered Channel.

Small example PBIX attached.

Regards,

Owen