Non Buying Customers

[negi007]

Hi Experts,

i need small help on an analysis. I am trying to find list of customers who did not buy a particular SKU. 

Transaction table
Customer_ID	SKU	Sales
Cust_1	SKU_1	20
Cust_1	SKU_1	30
Cust_1	SKU_2	40
Cust_1	SKU_3	50
Cust_2	SKU_1	20
Cust_2	SKU_2	30
Cust_2	SKU_2	40
Cust_2	SKU_2	50
Cust_3	SKU_1	20
Cust_3	SKU_1	30
Cust_3	SKU_3	40
Cust_3	SKU_3	50

Customer Table
Customer_ID	Customer_Name
Cust_1	abc
Cust_2	xyz
Cust_3	pqr
Cust_4	def

SKU Table
SKU_ID	SKU_Name
SKU_1	big
SKU_2	medium
SKU_3	small
SKU_4	xtra small

i have these tables and i need to find out count of customers not buying a particular sku out of total buying customers

Total Buying customers (any SKU)	Customers Not Buying SKU_1	Customers Not Buying SKU_2	Customers Not Buying SKU_3	Customers Not Buying SKU_4
3	1	1	1	3

have also attached pbix file. any help would be appreciated.- thanks in advance

[Padycosmos]

Hello Negi007,

Here's the link to the solution file. Hope this helps.

https://drive.google.com/file/d/1PXY2EPzRuGlzMtJPtUr658JFZfjMXyxW/view?usp=share_link 

[Greg_Deckler]

@negi007 Should be something like this:

Not Buying Measure =
  VAR __SKU = MAX('SKU Table'[SKU_ID])
  VAR __Customers = DISTINCT(SELECTCOLUMNS(ALL('Customer Table'),"Customer_ID",[Customer_ID]))
  VAR __CustomersBuying = DISTINCT(SELECTCOLUMNS('Transaction table',"Customer_ID",[Customer_ID]))
  VAR __Result = COUNTROWS(EXCEPT(__Customers, __CustomersBuying))
RETURN
  __Result