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.
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!
Solved! Go to Solution.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
My bad, I tried with the correct DAX expression and it worked! Thank you very much 😃
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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?
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |