Filter does not work

[m_e_1203]

Hello,

I am trying to count the number of the stores who met the target sales amount.

# of Store = CALCULATE(DISTINCTCOUNT(Store[StoreName]), FILTER(Sales, Sales[CustomerID]))
Difference from Target$ = [CY Sales] - SUM(Target[Target Amount])
*** CY sales = Current Year Sales Total ***

# of Store reached Target$ = CALCULATE([# of Store], FILTER(Sales, [Difference from Target$] > 0))

And this calcultion includes the stores has negative values. 

Why Filter calculation does not work for this case?

Example (Store Group1):   Total Store = 5 // # of store reached target = 3  --> This is correct result. 

CY Sales	Target Amount	Difference from Target$	Store Name
$1,203,199	$600,000	$603,199	Store A
$248,143	$210,000	$38,143	Store B
	$0	$0	Store C
	$0	$0	Store D
	$11	($11)	Store E

Example (Store Group2):  Total Store = 3 // # of store reached target = 3

CY Sales	Target Amount	Difference from Target$	Store Name
$119,488	$21,000	$98,488	Store F
$318,976	$443,187	($124,211)	Store G
$119,255	$329,601	($210,346)	Store H

Example (Store Group3):  Total Store = 4 // # of store reached target = 4

CY Sales	Target Amount	Difference from Target$	Store Name
$52,258	$65,000	($12,742)	Store I
$135,444	$149,431	($13,987)	Store J
$57,436	$94,000	($36,564)	Store K
$119,315	$184,241	($64,926)	Store L

Thank you.

[CNENFRNL]

@m_e_1203 , Highly likely that the issue results from filter propagation in your data model given that different columns were referenced in your measures. You'd better attach a dummy file so that it's much easier to troubleshoot.

[m_e_1203]

@CNENFRNL 

Thank you for your assistance.

I was able to create dummy data and faced same issue in the dummy file.  Please check.

https://jfc0.sharepoint.com/:u:/g/EY68C8WbLflCqtK6eDI2y3ABslnOZmudrxMxnGDsWlM41Q?e=yj0Oae

[v-lionel-msft]

Hi @m_e_1203 ,

Like this?

I think you just need to compare the [Total Sales] column with the [Target Amount] column.
But I'm not sure what you want to do with the row where [Target Amount]=0 or blank.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[m_e_1203 · Accepted solution]

Hi @v-lionel-msft 

Thank you for checking. There seems to be still not matching data but I could figure out counting different way.

Thanks again for your assitance.

[m_e_1203]

*** Correction *** 

Example (Store Group1):   Total Store = 5 // # of store reached target = 2  --> This is correct result. 

CY Sales	Target Amount	Difference from Target$	Store Name
$1,203,199	$600,000	$603,199	Store A
$248,143	$210,000	$38,143	Store B
	$0	$0	Store C
	$0	$0	Store D
	$11	($11)	Store E