Forum Discussion
COUNT based on a measure
Hello Guys, I'm brand new to Power BI and DAX and need some Help to execute the followin.
I have the following table
| Client | Target of Calls | Number of Calls Made | Above Target |
| Sales Person 1 | 18 | 19 | 1 |
| Client A | 10 | 9 | No |
| Client B | 8 | 10 | Yes |
| Sales Person 2 | 13 | 13 | 1 |
| Client C | 5 | 6 | Yes |
| Client D | 7 | 7 | No |
The [Target of Calls] is a calculated Measure that I create this way: Target of Calls = COUNT(PlannedCalls[CALL_ID])
The [Number of Calls Made] is also a calculated Measure calculated by the formula: Number of Calls Made = COUNT(Calls[Call_ID])
I need to create a new measure to identify how many Clients were Called ABOVE target .... The last column of above table. I was able to create a column to identify if it's above Target or Not .... but now I'm not able to aggregate it on the SalesPerson level .... any idea how do it ?
Thanks in advance
Anonymous not sure what does that means but I guess it answered your original question, no?
✨ Follow us on LinkedIn
Check 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.⚡
Hi,
Try this measure
Measure1 = countrows(filter(values(Customers[Customer name]),[Number of calls]>[Target of calls]))
Drag Measure 1 to a card visual.
11 Replies
- parry2k
Super User
Anonymous try this measure
Count Above = SUMX ( VALUES ( Table[Client Column] ), IF ( [Number of Calls] > [Target of Calls], 1, 0 ) )and use it in the visual.
✨ Follow us on LinkedIn
Check 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.⚡
parry2k almos there .... the only thing is that the total row for the salesrep is summing the whole table ...
maybe I need to use some other parameter to achieve it .
and thanks a lot for the help already
- parry2k
Anonymous not sure what does that means but I guess it answered your original question, no?
✨ Follow us on LinkedIn
Check 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.⚡
parry2k part of It, I can make the YES or NO but the part in red below its summing all the register in client table, despite the filterw I'm using in the view
Client Target of Calls Number of Calls Made Above Target Sales Person 1 18 19 1 Client A 10 9 No Client B 8 10 Yes Sales Person 2 13 13 1 Client C 5 6 Yes Client D 7 7 No
- parry2k
Anonymous I'm still confused, you need to show the result using my measure, paste sample data, show the result what is working and what is not working? I'm shooting here in the dark with very little information
- Ashish_Mathur
Hi,
Try this measure
Measure1 = countrows(filter(values(Customers[Customer name]),[Number of calls]>[Target of calls]))
Drag Measure 1 to a card visual.
Hi Ashish.....
Well, I think I have missed something. ...... your formula did the same as the parry2k did ...
The thing is that there is a filter .... for Client A and Client B for instance ... As per the test I did , its summing the clients despite the filter, só te total on Sales Person is "wrong"
I'm sorry I cannot share database print as it's my company confidential
- Ashish_Mathur
Hi,
I cannot help you without seeing your file.
- v-xulin-mstf
Community Support
Hi Anonymous
Try measure as:
Count Above = IF( HASONEVALUE('Table'[Client]), IF(MAX('Table'[Number of Calls Made])>MAX('Table'[Target of Calls]),"Yse","No"), IF( SUMX(FILTER(ALL('Table'),'Table'[Sales Person]=MAX('Table'[Sales Person])),'Table'[Number of Calls Made])>SUMX(FILTER(ALL('Table'),'Table'[Sales Person]=MAX('Table'[Sales Person])),'Table'[Target of Calls]), "Yes", "No" ) )Here is the output:
The demo is attached, please try it.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
