Problem with Distinct Count of # Number of Customers

[Anonymous]

Hi all,

I'm breaking my head about the following. 

I made little salesexample which shows my problem (see picture below).
As an example I want to know how many distinct customers made orders with Contoso.

The measure I use is 

# Distinct Customers who baught Contoso = CALCULATE(DISTINCTCOUNT(SalesExample[CustomerKey]), SalesExample[Brand] = "Contoso")

Offcourse this measure is working. But now I want to know how to measure the distinct customers who didn't have orders with Contoso. Offcourse I can subtract my last measure from the total of Distinct Customers that had sales, but I want to do in another way.
As you can see 6 customers had orders with Contoso, where there are 10 distinct customers with orders. So the number should be 4 for the customers without Contoso orders.

I made the following measure:

Distinct Customers without Contoso = CALCULATE(DISTINCTCOUNT(SalesExample[CustomerKey]), SalesExample[Brand] <> "Contoso")

I thought this would be working, but apperently it gives me an outcome of 5 as you can see in the picture.
The problem seems to be within customer nr 4 who had an order with contoso and an order without Contoso. But in my opinion that is a customer who had an order with contoso, so it shouldn't be taken into the results of my last measure.

Can someone help?

Thanks in advance

 

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

That's by default. As In your formula , we will get the DISTINCTCOUNT of CustomerKey of the filtered table that SalesExample[Brand] is not"Contoso". So the result 5 should be correct.

Distinct Customers without Contoso = CALCULATE(DISTINCTCOUNT(SalesExample[CustomerKey]), SalesExample[Brand] <> "Contoso")

So in your scenario, to get your excepted result we can update the measure as below.

Distinct Customers without Contoso = 
VAR con =
    CALCULATETABLE (
        VALUES ( SalesExample[CustomerKey] ),
        FILTER ( SalesExample, SalesExample[Brand] = "Contoso" )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( SalesExample[CustomerKey] ),
        FILTER ( SalesExample, SalesExample[CustomerKey] IN con = FALSE () )
    )

[Anonymous]

Hi @v-frfei-msft 

Thanks for your quick reply.

Your new measure seems like working perfect, but I still can't stand it that my measure is calculating the wrong result ;).......And still it's no logic to me.

But thanks again!