Count of Customers with no Sales

[Geniebelle123]

Hello Experts,

I need to create a card that show the Count of Customers with no Sales filtered by date and brand. I tried the following measure

No of Customers with no Sales =

CALCULATE(

COUNT(Customer[Customerid]),

FILTER(salesinvoices,[Count of Sales invoice]=0)

)

But it always show blank.

Thanks all in advance~

This is my sample data.

Sales Invoice
Customer ID	Total Amount	Date	Sales Invoice ID
A	100	43101	ID1
B	200	43133	ID2
C	300	43134	ID3
D	400	43194	ID4

Invoice Line
Invoice Line ID	Sales Invoice ID	Brand
LINE1	ID1	Brand A
LINE2	ID2	Brand B
LINE3	ID3	Brand A
LINE4	ID4	Brand B
LINE5	ID1	Brand A
LINE6	ID2	Brand B
LINE7	ID3	Brand A
LINE8	ID4	Brand B

Customer

Customer ID

A

B

C

D

E

[Ashish_Mathur]

Hi,

Based on the data you have shared, please let us know the exact result you are expecting and rationale for the same.

[Geniebelle123]

@Ashish_Mathur

This is my expected result!

Thanks

[v-cherch-msft · Accepted solution]

Hi @Geniebelle123

You may create the measures as below and change the relationships. Attached the sample file.

Measure =
IF (
    NOT ( MAX ( Customer[Customer ID] ) ) IN VALUES ( 'Sales invoice'[Customer ID] ),
    1
)

Customers with no sales = SUMX(Customer,[Measure])

Regards,

Cherie

[Kevin_Gitonga]

Hi, this works when comparing all customers in the customers table however I need to filter out the customers table to consider only the customers who have bought in the last one year. How can I go around this?

[v-cherch-msft]

Hi @Geniebelle123

Could you show us the measure [Count of Sales invoice] you created? Please show the relationships. If you could share the .pbix file, we could provide an accurate solution. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

Cherie

[Geniebelle123]

Hi @v-cherch-msft

This is my sample data in pbix file

https://drive.google.com/drive/folders/1A6sS4WCkFsBHwVgYI3JzGHVmuAE5hhw-?usp=sharing

Thanks