Divide with a filtered measure

[sdavis]

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]))

[Ashish_Mathur]

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%.

[sdavis]

@Ashish_Mathur thank you for catching that, I edited the post. It should be 40%. 

[Ashish_Mathur · Accepted solution]

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]))

[MegosaurusX]

Thank you sooooo much for this. Gahhh, I was struggling.

[Ashish_Mathur]

You are welcome.

[sdavis]

Thank you @Ashish_Mathur that worked. 

Is there a way I can show the percentage as well? 

[Ashish_Mathur]

Hi,

Just format the measure as a % age.

[sdavis]

@Ashish_Mathur I thought of another use case for the formula. Is there a way we can filter out the blank values to make responses under 25 hours and over 0.01 hours? I tried to filter between 25 hours and 0, but it didn't filter the blank values out. 

Here's the formula:

Tickets Responded (24hrs) = CALCULATE(COUNT('Week'[Ticket ID]), FILTER('Week', 'Week'[First response (hrs)] < 25 && 'Week'[First response (hrs)] >= 0.01))

[Ashish_Mathur]

Hi,

I am not clear.  Share yoru dataset and show the expected result.

[sdavis]

Are you able to download my .pbix file from the link?

https://www.dropbox.com/s/0i30dtuzkm0l1bu/Example.pbix?dl=0 

[Ashish_Mathur]

Hi,

Just filter the measure int he visual filter section (look at the visualisation pane).

Hope this helps.

[sdavis]

Is there a way I can just filter a measure shown on a card without the entire page?

My new link shows an example with other charts included: https://www.dropbox.com/s/3uoxzx93srmowmd/Example_help.pbix?dl=0 

[Greg_Deckler]

The error that you are getting, you need to end your first COUNT function with a ) just before your ",". 

[sdavis]

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?