Forum Discussion
Create a measure/calculated field
Basically, I’ve created the table below. It calculates New Tickets, Closed Tickets, Open Tickets, etc. dynamically.
We would like to enter a new field that has a static number for each category tier, called “benchmark”. We would then like to be able to calculate the % of the tickets that had been closed that were closed within the benchmark, and the number of tickets open that have been open longer than the benchmark.
I’ve created a benchmark table, but have no idea how to tie it to my current calculations. Any thoughts?
Ideally, the table that would be created would be something like:
Category Tier | New Tickets | Closed Tickets | Open Tickets | Benchmark | % tickets closed within benchmark | % of open tickets greater than benchmark |
MDA Data Issue | 56 | 30 | 18 | 12.2 | 77% | 5% |
MDA Login/Access | 30 | 23 | 27 | 1.4 | 47% | 50% |
MDA New Item/Element or Modify | 82 | 16 | 22 | 20.6 | 59% | 94% |
MDA New Site or Modify | 59 | 16 | 10 | 13.7 | 88% | 39% |
MDA Other | 73 | 49 | 25 | 11.1 | 66% | 36% |
MDA Report – I Need help | 39 | 8 | 10 | 11.4 | 31% | 85% |
MDA System UI Issue/Outage | 76 | 8 | 11 | 11.6 | 17% | 2% |
MDA Training | 56 | 46 | 20 | 8.1 | 12% | 36% |
MDR Data Issue | 14 | 1 | 11 | 13.3 | 34% | 3% |
MDR Login/Access | 94 | 71 | 28 | 11.1 | 97% | 60% |
MDR New Item/Element or Modify | 83 | 71 | 25 | 8.3 | 99% | 85% |
MDR New Site or Modify | 69 | 45 | 28 | 7 | 98% | 7% |
MDR Other | 16 | 1 | 27 | 26.9 | 7% | 32% |
MDR Report – I Need help | 69 | 59 | 18 | 9.7 | 88% | 78% |
MDR System UI Issue/Outage | 25 | 10 | 23 | 6 | 57% | 98% |
MDR Training | 88 | 44 | 29 | 12 | 53% | 62% |
WEDCS Help using the tool | 81 | 6 | 30 | 11.9 | 49% | 63% |
WEDCS Login/Access | 100 | 52 | 21 | 7 | 61% | 66% |
WEDCS Question | 12 | 11 | 28 | 9 | 15% | 44% |
WEDCS Training | 88 | 43 | 26 | 12.8 | 63% | 1% |
Any thoughts or advice or ideas on where I can look?
Thanks,
Nick
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.
