Customers have different services .Find the number of Customers with more than one service

[Anonymous]

I have a table as follows. 

Client_ID		ClientName		ClientProduct		Site		BilledAmount
1		Client A		Product A		Site A		2300
1		Client A		Product A		Site B		2550
1		Client A		Product B		Site A		2340
2		Client B		Product A		Site A		2700
2		Client B		Product C		Site A		3400
3		Client C		Product B		Site A		4500
4		Client D		Product D		Site A		3600

Different clients have products. These products could be on different or the same sites of the client. I would like to know. 

1. How many clients have more than 1 ClientProduct?

2. How many clients have more than one 1 clientProduct in the same site ?

Just submit your answer if you can tackle any of them..
Thanks.

[Ritaf1983 · Accepted solution]

Hi @Anonymous 
For first request try the measure :

DistinctClientCount =

CALCULATE(

    DISTINCTCOUNT('table'[Client_ID]),

    FILTER(

        'table',

        CALCULATE(

            DISTINCTCOUNT('table'[ClientProduct]),

            ALLEXCEPT('table', 'table'[Client_ID])

        ) > 1

    )

)

For the second :

DistinctClientCountSameSite =

CALCULATE(

    DISTINCTCOUNT('table'[Client_ID]),

    FILTER(

        'table',

        CALCULATE(

            DISTINCTCOUNT('table'[ClientProduct]),

            ALLEXCEPT('table', 'table'[Client_ID], 'table'[Site])

        ) > 1

    )

)

Link to a sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[TomMartens · Accepted solution]

Hey @Anonymous , 

you can create two calculated columns like so:

client has more than product = 
var currentClientID = 'Table (2)'[Client_ID]
var noOfPrudcts =
    CALCULATE( 
        DISTINCTCOUNT( 'Table (2)'[ClientProduct] )
        , ALL( 'Table (2)' )
        , 'Table (2)'[Client_ID] = currentClientID
    )
return
IF( noOfPrudcts > 1 , "more than one product" , "only one product" )

and so:

spread across sites = 
var currentClientID = 'Table (2)'[Client_ID]
var noOfSites =
    CALCULATE( 
        DISTINCTCOUNT( 'Table (2)'[Site] )
        , ALL( 'Table (2)' )
        , 'Table (2)'[Client_ID] = currentClientID
    )
return
IF( noOfSites > 1 , "more than one site" , "only one site" )

The table then will look like this:

I decided not to create a measure because I assume it's helpful if you can use these columns inside slicers.

Hopefully, this provides what you are looking for.

[Ritaf1983 · Accepted solution]

Hi @Anonymous 
For first request try the measure :

DistinctClientCount =

CALCULATE(

    DISTINCTCOUNT('table'[Client_ID]),

    FILTER(

        'table',

        CALCULATE(

            DISTINCTCOUNT('table'[ClientProduct]),

            ALLEXCEPT('table', 'table'[Client_ID])

        ) > 1

    )

)

For the second :

DistinctClientCountSameSite =

CALCULATE(

    DISTINCTCOUNT('table'[Client_ID]),

    FILTER(

        'table',

        CALCULATE(

            DISTINCTCOUNT('table'[ClientProduct]),

            ALLEXCEPT('table', 'table'[Client_ID], 'table'[Site])

        ) > 1

    )

)

Link to a sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly