cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

## Count of Customers with no Sales

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.

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
1 ACCEPTED SOLUTION
Microsoft

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
New Member

@Ashish_Mathur

This is my expected result!

Thanks

Microsoft

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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?

Microsoft

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
New Member

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors