Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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 , 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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
*** 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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |