Forum Discussion
Count YTD Sales above specific value
Hi all,
Very new with Power BI and hope someone can help me.
I am trying to count the number of clients that have YTD sales above a certain threshold. I have the following two tables:
First Table
Name: DATE
| Date | Year | Month | Month Number | Quarter |
| 01.07.2021 | 2021 | July | 7 | Q3 |
| 02.07.2021 | 2021 | July | 7 | Q3 |
| ... | ... | ... | ... | ... |
Second Table
Name: SalesData
| Reporting Period | ClientName | Sales |
| 1 September 2021 | Apple | 10 |
| 1 September 2021 | Banana | 30 |
| 1 September 2021 | Orange | 50 |
| 1 October 2021 | Apple | 10 |
| 1 October 2021 | Banana | 15 |
| 1 October 2021 | Orange | 60 |
| 1 November 2021 | Apple | 15 |
| 1 November 2021 | Banana | 50 |
| 1 November 2021 | Orange | 60 |
Now, I have used the following measure to calcuate the YTD Sales, which a working fine:
Sales_Sum = SUM('SalesData'[Sales])
Sales_YTD = IF(Sales_Sum<>Blank(), Calculate([Sales_Sum], DATESYTD['Date'[Date])))
Finally, I am not able to get the correct count of clients above a threshold of 40 YTD Sales per month. The count are incorrect for the month of August (see screenshot below). I used the following measure:
CountFilter_YTD = CALCULATE(Count('SalesData'[ClientName]), FILTER('SalesData', [Sales_YTD]>40)
Would greatly appreciate some guidance!
powerbanana you didn't used the same DAX expression what I gave to you, not sure what else to tell
CountFilter_YTD = COUNTX ( VALUES ( Table[Furit] ), IF ([Sales_YTD]>40,1) )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
5 Replies
- parry2k
Super User
powerbanana try this
CountFilter_YTD = COUNTX ( VALUES ( Table[Furit] ), IF ([Sales_YTD]>40,1) )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thanks for the prompt feedback. Unfortunately, your proposed solution has the same issue has my original measure. It seems that the filter is applied on the Sales_Sum values, but not the Sales_YTD.
I used your proposal in a similar & larger table, but the count is still incorrect (see below November and October are missing counts). Any other ideas?
- parry2k
powerbanana you didn't used the same DAX expression what I gave to you, not sure what else to tell
CountFilter_YTD = COUNTX ( VALUES ( Table[Furit] ), IF ([Sales_YTD]>40,1) )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
My bad, I tried with the correct DAX expression and it worked! Thank you very much 😃
- parry2k
powerbanana sounds good.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
