topic Re: Countdistinct with filter on aggregated measure in DAX Commands and Tips

Countdistinct with filter on aggregated measure

GreenP — Tue, 31 Jan 2023 07:53:14 GMT

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

Re: Countdistinct with filter on aggregated measure

wdx223_Daniel — Tue, 31 Jan 2023 08:24:39 GMT

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)))

Re: Countdistinct with filter on aggregated measure

GreenP — Tue, 31 Jan 2023 11:10:42 GMT

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.

Re: Countdistinct with filter on aggregated measure

wdx223_Daniel — Wed, 01 Feb 2023 00:04:43 GMT

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