Create a measure/calculated field

[nmartinovic]

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

[Greg_Deckler]

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.

[nmartinovic]

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?

[ALeef · Accepted solution]

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.

[PowerBIGuy]

You can join benchmark table by category tier and than create a calculated measure that divides your benchmark value by row count to make it a static number.