Count of sites not purchasing

[davehus]

Hi,

I want to work out how many of my customers are not buying from my suppliers for the current year when I select a filter.
I have a customer table which has customers from the last 2 years. When I write the measure it includes all the customers from table whether they have bought this year or not when I use the All function.

Any thoughts on how I can correct this.

[v-chuncz-msft · Accepted solution]

@davehus,

Instead of ALL Function, check ALLSELECTED Function.

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[davehus]

Hi,

Customer Name	Supplier	Sales CY
Customer C	Supplier B	1000
Customer A	Supplier B
Customer D	Supplier B	500
Customer B	Supplier B	510

Expected result is to show that the supplier sells product to 75% of the customers in the sample table above. The part I'm struggling with is that their are customers in my Customer Table that haven't purchased this year and I want to exclude them from the evaluation. Hope this makes sense. 🙂

Thanks

[Ashish_Mathur]

Hi @davehus,

Try this formula

=COUNT(Table1[Sales CY])/DISTINCTCOUNT(Table1[Customer Name])

Hope this helps.

[davehus]

Hi Asish,

Thanks for this.

Sales CY is a measure and not a column. I'm currently using this logic to determine how many sites are supplied.

Delivery Points Supplied = CALCULATE(DISTINCTCOUNT(Customer[Customer Number]), FILTER(Customer, CALCULATE([Sales CY]>0))) - This works fine for me.

However when I write the measure to capture sites that are not purchasing from the selected supplier, it includes all my customers when I only want to return customers that have purchased this year.

Hope this makes sense.

[Ashish_Mathur]

Hi,

Share the link from where i can download the file and show the expected result there (actual figure which you are expecting the formula to return).

[TomMartens]

Hey,

w/o more information about the tables contained in your data model and how these tables are related it will become a little difficult to help you with any DAX statement either a measure or a calculated column.

Maybe this post addresses a similar requirement:

https://community.powerbi.com/t5/Desktop/How-to-build-a-white-space-report/m-p/269666#M121457

Hopefully this gets you started

Regards

Tom