Forum Discussion
Create a measure/calculated field
This is the classic "Events in Progress" Problem I dealt with just a few weeks ago.
http://blog.crossjoin.co.uk/2011/11/10/solving-the-events-in-progress-problem-in-dax-v2-0/
The only way I was able to solve it was to have a calc cloumn that has the "Days Open" for each ticket. Then you can filter by category. Right now you are calculating average time open, which is nice to know, but not what you're looking for in this measure. Alternatively, you could make something like : overdue = if (days open > <target number>, 1,0) and do a countif based on category && overdue divided by countif category.
Hopefully that makes sense, still first cup of coffee.
In Desktop, third icon down on the left is the relationship canvas. Your tables "Tickets" and "Benchmarks" will be displayed just drag Category Tier to Category Tier to create the relationship.
Then, create two measures:
% tickets closed within benchmark = COUNTAX(FILTER('Tickets',[Days to Closure] <= 'Benchmarks'[Benchmark]),[Description]) / COUNTA('Tickets'[Description]
% of open tickets greater than benchmark = COUNTAX(FILTER('Tickets',[Days to Closure] > 'Benchmarks'[Benchmark]),[Description]) / COUNTA('Tickets'[Description]
Obviously I'm taking some liberties with naming since I don't have your exact data and model, but something along these lines.
Put these into a table or matrix with "Category Tier", etc, and since the measures are context sensitive, should all work.
Thank you very much for your input - I don't believe that I was clear earlier. New Tickets, Closed Tickets, and Open Tickets are measures based on my data already.
m New Tickets = IF(COUNTROWS(VALUES('Sheet1 (2)'[Month]))=1, COUNTROWS(FILTER(IncidentSet, IncidentSet[Ticket Month (New)] = VALUES('Sheet1 (2)'[Month]) && 'IncidentSet'[StateCode.Value] <> 2)))
m Open tickets = IF(COUNTROWS(VALUES('Sheet1 (2)'[Month]))=1, COUNTROWS(FILTER(IncidentSet, IncidentSet[Ticket Date (New)] <= VALUES('Sheet1 (2)'[End of Month]) && (IncidentSet[Ticket Date (Closed)] > VALUES('Sheet1 (2)'[End of Month]) || [StateCode.Value] = 0))))
m ClosedTickets = IF(COUNTROWS(VALUES('Sheet1 (2)'[Month]))=1, COUNTROWS(FILTER(IncidentSet, IncidentSet[Ticket Month (Closed)] = VALUES('Sheet1 (2)'[Month]) && 'IncidentSet'[StateCode.Value] = 1)))
So it would seem like the measures that I need to create:
% tickets closed within benchmark = (m closed tickets where Days open > Benchmark)/m closed tickets)
No idea how to do that. Any thoughts?
This is the classic "Events in Progress" Problem I dealt with just a few weeks ago.
http://blog.crossjoin.co.uk/2011/11/10/solving-the-events-in-progress-problem-in-dax-v2-0/
The only way I was able to solve it was to have a calc cloumn that has the "Days Open" for each ticket. Then you can filter by category. Right now you are calculating average time open, which is nice to know, but not what you're looking for in this measure. Alternatively, you could make something like : overdue = if (days open > <target number>, 1,0) and do a countif based on category && overdue divided by countif category.
Hopefully that makes sense, still first cup of coffee.