Forum Discussion
Divide with a filtered measure
Hi,
I am new to PowerBI and trying to a measured columns in PowerBI for displaying a Card. I'm trying to divide with a filter, but getting an error (screenshot below). How can I devide with a filtered measure? I created a formula beforehand for the First response (hrs) column.
I tried referencing this link: Divide with a filter
Formula:
Tickets responded = Count of tickets responded under 25 hrs/Count of tickets
Data:
| Ticket ID | Created time | Initial response time |
| 1234 | 2018-04-15 06:14:10 | 2018-04-16 08:44:45 |
| 1235 | 2018-04-16 00:09:10 | |
| 1236 | 2018-04-16 02:41:51 | |
| 1237 | 2018-04-16 02:45:14 | |
| 1238 | 2018-04-16 07:24:58 | 2018-04-16 07:55:06 |
| 1239 | 2018-04-16 10:37:07 | 2018-04-16 12:22:33 |
| 1240 | 2018-04-16 11:21:12 | 2018-04-16 15:41:24 |
| 1241 | 2018-04-16 11:28:37 | 2018-04-16 15:41:40 |
| 1242 | 2018-04-16 12:04:47 | |
| 1243 | 2018-04-16 13:14:37 | 2018-04-17 10:43:21 |
1st formula: First response (hrs) = DATEDIFF('Sheet1 (2)'[Created time], 'Sheet1 (2)'[Initial response time], HOUR)
2nd formula: (not working)
Tickets Responded = DIVIDE(CALCULATE(COUNT('Sheet1 (2)'[Ticket ID], 'Sheet1 (2)'[First response (hrs)] < 25)),COUNT('Sheet1 (2)'[Ticket ID]))
Hi,
Does this work?
=DIVIDE(CALCULATE(COUNT('Sheet1 (2)'[Ticket ID]),FILTER(Sheet1 (2), 'Sheet1 (2)'[First response (hrs)] < 25)),COUNT('Sheet1 (2)'[Ticket ID]))
14 Replies
- Greg_Deckler
Community Champion
The error that you are getting, you need to end your first COUNT function with a ) just before your ",".
Thank you Greg_Deckler
That fixed the formula error, but it didn't show the result I expected. When showing the result in a card, it showed 1. I was trying for the result to be 4.
Am I using the right formula for trying to divide tickets under 25hrs by the total?
- Ashish_Mathur
Super User
Hi,
How can tht division yield the result as 6? The number of records with a response time as < 25 hours is 4. 4/10 should be 40%.
Ashish_Mathur thank you for catching that, I edited the post. It should be 40%.
- Ashish_Mathur
Hi,
Does this work?
=DIVIDE(CALCULATE(COUNT('Sheet1 (2)'[Ticket ID]),FILTER(Sheet1 (2), 'Sheet1 (2)'[First response (hrs)] < 25)),COUNT('Sheet1 (2)'[Ticket ID]))
