Reasons to simple CALCULATE with DISTINCTCOUNT and FILTER doesn't working correctly on card

[gluizqueiroz]

I have a table with sales, normal table (Customer_Id, Customer_Name and Sale_Value).

I have been trying to create a simple measure that returns me the DISTINCTCOUNT of Customers that bought more than $1000, just this. 

MeasureWithProblem = CALCULATE(DISTINCTCOUNT(Table[Customer_Id]); FILTER(Table; SUM(Table[Sale_Value]) > 1000))

If I put on a visual table the following columns: Customer_Id, Customer_Name, Sale_Value, MeasureWithProblem), I have the followin results:

Customer_ID	Customer_Name	Sale_Value(sum)	MeasureWithProblem
1	Jonh Doe	11533	1
2	Jonh Doe	7476	1
3	Finn	2338	1
4	Poe	1824	1
5	Ren	332

So far it's good, but if I put the same "MeasureWithProblem" on a card, I have the number "5".

My problem is: Why I have the number 5 if I don't have 5 customers with more than 1000 of sales?

I use CALCULATE(func; FILTER(table; condition)) all the time and I really don't know why it's happenning now. 

Here some date to test:

Customer_Name	Sale_Value	Customer_Id
Jonh Doe	791.64	1
Jonh Doe	353.64	2
Finn	105.12	3
Finn	112.32	3
Finn	237.84	3
Jonh Doe	394.32	1
Finn	140.6	3
Finn	184.38	3
Finn	375.36	3
Finn	266.4	3
Finn	86.04	3
Finn	155.28	3
Finn	70.26	3
Finn	37.84	3
Finn	53.07	3
Poe	143.45	4
Poe	59.28	4
Poe	129.45	4
Jonh Doe	391.2	1
Jonh Doe	292.8	1
Jonh Doe	328.32	1
Jonh Doe	394.2	1
Jonh Doe	505.02	1
Jonh Doe	3560	1
Jonh Doe	819.2	1
Jonh Doe	68.88	2
Jonh Doe	273.6	2
Jonh Doe	574.8	2
Jonh Doe	1068	2
Ren	3283.2	5
Ren	8250	5

[Iamnvt · Accepted solution]

hi,

if you show the total for the table, it will appear the result as "5" also.

The reason is for the total value, it consider having no filter on "Customer_ID" column.

Your measure uses SUM('Table'[Sale_Value]), so when no "customer_ID" is selected, the SUM is for the whole "Table" table, which has the value > 1000, so the after-filtered table is kept the same (no filter). With that same table, it has 5 "Customer_ID" in the after-filtered Table.

To get the Measure works, you need to change to:

MeasureWithProblem = CALCULATE(DISTINCTCOUNT('Table'[Customer_ID]), FILTER('Table', 'Table'[Sale_Value] >1000))

each Rows in the Table will be evaluated, not the SUM as your previous measure.