Countdistinct with filter on aggregated measure

[GreenP]

Hi Experts,

We have a table "SALES" like below:

Month	Zone	Product	Customer	Sales Target	Amount Billed
2022-01	EAST	PR01	CUST01	12
2022-02	EAST	PR01	CUST02	15	11
2022-03	EAST	PR01	CUST03		16
2022-01	WEST	PR01	CUST04	10	15
2022-02	WEST	PR01	CUST05	17
2022-03	WEST	PR01	CUST06	10	19
2022-01	EAST	PR02	CUST01		17
2022-02	EAST	PR02	CUST02	14	13
2022-03	EAST	PR02	CUST03		17
2022-01	WEST	PR02	CUST04	11	11
2022-02	WEST	PR02	CUST05	14
2022-03	WEST	PR02	CUST06		20

We need to show the following calculated measures by Zone and Product in a matrix table:

Count of Customers Targeted = distinct count of CUSTOMER where Sum(Sales Target) is >0

Count of Customers Billed = distinct count of CUSTOMER where Sum(Amount Billed) is >0

Count of Customers Targeted but Not Billed = distinct count of CUSTOMER where Sum(Sales Target) is >0 and Sum(Amount Billed) is <=0

Month to be used as slicer (multiple selection).

If anyone can help with the DAX for the three calculated measures stated above, it would be of great help.

Thanks

[wdx223_Daniel · Accepted solution]

Count of Customers Targeted = COUNTROWS(FILTER(VALUES(Table[Customer]),CALCULATE(Sum(Table[Sales Target]))>0))

Count of Customers Billed = COUNTROWS(FILTER(VALUES(Table[Customer]),CALCULATE(Sum(Table[Amount Billed]))>0))

Count of Customers Targeted but Not Billed = COUNTROWS(FILTER(VALUES(Table[Customer]),CALCULATE(Sum(Table[Amount Billed])<=0&&Sum(Table[Sales Target])>0)))

[GreenP]

It works like a charm !! 

One more request: can we create another measure :

Total Sales Target of Customers Targeted but Not Billed = Sum(Sales Target) of the CUSTOMERs where Sum(Sales Target) is >0 and Sum(Amount Billed) is <=0

Thanks a lot in advance.

[wdx223_Daniel]

CALCULATE(Sum(Table[Sales Target]),FILTER(VALUES(Table[Customer]),CALCULATE(Sum(Table[Amount Billed])<=0&&Sum(Table[Sales Target])>0)))