Forum Discussion
Filter does not work
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.
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.
5 Replies
*** 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 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
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
