Help creating a count of customers of BusinessA who are also customers of BusinessB

[blodzoom]

I'm trying to create a count of customers of BusinessA who are also customers of BusinessB.  In other words, the overlap of customers.  On the surface, this would be pretty simple measure but I want to be able to filter for business.

I currently have one table:

Business    Region  CustomerName   CustomerNumber

I have slicers for region and business.  If I select A, I have a card that gives me the distinct customerNumber count in that business.  But now I want another card that tells me the distinct customerNumber count  of those customers of business A that are also customers of BusinessB.

If I filter for business A, I'm filtering out all of the business B customers, so I can't count them.

I tried creating a second data set that was just a list of distinct customers of BusinessB thinking that I could just create the relationship on customernumber and use that to count common customers but the filters don't filter the new data set (is it because the many side can't filter the one side?)

I'm having trouble getting my head around this seemingly simple predicament.  Any assistance would be greatly appreciated.

[v-yulgu-msft]

Hi @blodzoom,

Add a calculated column in data table.

IsOverlap =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table 1'[Business] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[CustomerName], 'Table 1'[Region] )
    )
        > 1,
    "Y",
    "N"
)

Measures added to card:

Count for current Business =
DISTINCTCOUNT ( 'Table 1'[CustomerName] )

Count Overlap customer =
CALCULATE (
    DISTINCTCOUNT ( 'Table 1'[CustomerName] ),
    FILTER ( 'Table 1', 'Table 1'[IsOverlap] = "Y" )
)

Best regards,

Yuliana Gu

[blodzoom]

Thank you for your reply.

I think that you hit what I asked, unfortunately, I was not clear in my ask.  I'm wondering if this can be modified to cover an extra bonus complexity:

Overlap customers aren't defined as customers who are customers of multiple businesses (though that is possible), they are customers who are members of a customer family of the filtered business.

ABC INC is the customer family which includes ABC North America and ABC South America etc... I have 4 cards (one for each business).  If I filter for business A and just one ABC INC affiliate is a customer of business A, The A card should say 1.  The B card should be a count of ABC INC affiliates that are customers of business B, regardless of whether or not they are customers of business A. 

If I filter for business A and there are 100 customers of business A, all falling into 10 families.  I want to look at those 10 families, count the distinct customers for each of the other 3 businesses that fall under those families and have that number reflected on their respective cards.

I hope this is clear, and I hope that somebody can help me because I had trouble sleeping last night trying to think through possible solutions.

[v-yulgu-msft]

Hi @blodzoom,

To make your question more clear, please provide sample data and show us your desired output with examples.

Regards,

Yuliana Gu