Excel Chart Question

Le_Goat

I've exhausted my Excel guru options, and Google searches aren't pulling up much, so I figured why not try in here.

I am trying to create a visual representation of performance metrics for my team, where one chart can show us a ton of options. I also need this chart to be dynamic in that I can run the numbers once a week over a four-week period. The chart will capture the average Time to First Response (TFP), broken down by week:


Here's the part where I'm running into issues. I'd like the have each of those bars segmented so that they are each broken down by ticket type. The problem is that I can't use a default segmented chart, because that puts everything at 100%. I also can't do stacked because it throws those totals on top of the TFP bars, which doesn't help. Does anyone know how to get the existing TFP bars to become segmented without messing up the TFP totals in the chart?

If this isn't clear, I'm more than willing to elaborate.

tsmvengy

Not entirely sure, but I think you are going to want a pivot table/pivot chart. Lemme see if I can find an example.

tsmvengy

Does this look like what you want for an end product?

Le_Goat

Yeah, I'm using a pivot table/chart. The closest I can get right now is this:


It's using a combo chart with a secondary axis, but even then I can't get the bars to split up so they can show the breakdown of ticket types.

Le_Goat

tsmvengy wrote: »

Does this look like what you want for an end product?

That's where I keep ending up, but I actually want Segment 2 to be the only bar showing, but then break down segment 2 by what segment 1 has listed, so that the segment 2 bars are segmented further, if that makes sense. That is ideally where I want to be, but my combo chart is the closest I've been able to get so far.

schuss

To confirm - you want bars with counts of ticket volume (by type) then a line with avg. response time?

I think you may be able to get there using a cluster bar, but they won't stack the bars (as that won't allow comparability across ticket types). I would personally do this as a slicer which would let people select all or specific ticket types. I'd have to see the data to do it correctly though, as excel charting is half about just messing with settings IMO. That's why I usually use a BI tool to do it...

tsmvengy

Oh, so you have two completely different types of data, only one of which is segmented. What does your data table look like?

Looks like what you want is to take what I have (segment = ticket type) and then add a line graph on top of it?

Le_Goat

The line chart overlaying the bar is an option I threw together. What I'd ideally like to get is this:


but have each of those bars broken down like this


but making sure that the bars still show the same dimensions as the top chart. So my team could look at the average TFP, but inside that bar they could see we had X many of Y request type, and that may have been why bla bla bla happened.

I can scrub the data and share it if you'd like. It'll give you something to work with. I appreciate the help. It's driving me nuts.

tsmvengy

Like this?



For reference, here's what my underlying data looks like:



EDIT: Don't pay attention to the values, I'm using the RANDBETWEEN function to fill cells so they change when you do anything.

Le_Goat

I crafted a mock of what I'm hoping to obtain. It required copying parts of one chart, then skewing it to fit the original chart. Hope this clarifies what I'm hoping for. Sorry for any confusion.


The main bar shows the average TFP, but each bar is broken down further to show the ratio of request types that came in.

EDIT: Here's a sample of the data to provide reference:

Le_Goat

Here's the data if you want to fudge around with it:

tsmvengy

I'm not sure why you would want to do that - I think it would be confusing for the people looking at the data. The ticket requests by type aren't a portion of the average time to first response.

Doing a chart with split axes and showing the data separately will be able to show another relationship - how the total number of ticket requests affects the average time to response.

I think if you wanted your end result, you would have to create a series of tables to manipulate the data - turn the ticket type counts into percentages, then apply those percentages to the average time to 1st response each week. Then you can make a table with that data and it will have the end result you're looking for.

But again, I think the end result you're looking for is a bit nonsensical. You'll be implying that time spent on each of those ticket types somehow adds up to a total average time to 1st response, which isn't right.

Elvenshae

Le_Goat, the problem is that what you want to show isn't really a good chart concept.

You're mixing things - where the height of the bar matters, but the cuts of the pieces within it actually have nothing to do with the height. Reading your chart, the first thing people are going to think is that the sum of the pieces adds up to the total height value, but that's not true at all; it's not even a contributory value.

It's better to split this up into two charts - a simple line or (preferably) column chart that shows time to first response, and then a second chart that shows the breakout by type.

If you absolutely have to do things like that, then you'd do it in two steps. The first is to calculate the average TFP for the period, and the second would be to multiply it by the share of issue type to come up with a "pseudo-contributory" value. Then, chart the sums of those, and just delete the total. Should look something like this:

Elvenshae

(should be "Average Time to First Response", but you get the idea)

Elvenshae

Something like this would probably work better:

schuss

Yeah, if they're individual time to first response, you want a line graph with the different options and separate bars for ticket volume. You're mixing data types which will either confuse people or distort the message the data is supposed to convey.
I'd recommend either separating (as mentioned above) or using a slicer so people could only look at specific groupings together.

Mugsley

I think you all just wanted a reason to show off your Excel skills you've been hiding from the public.

Le_Goat

Quick background: My team is engaged in root core problem solving, so the team works together to figure shit out. I'm leading the team through this with the goal of creating performance metrics instead of just reporting on number tracking.

I fully agree that it doesn't make much sense to do it this way. The head of our team really wanted to see something like what I'm asking for in this, but to me it falsely makes some items look like more than other items. If say there were 30 type A in one week and 15 type A in the second week, Week 2 could make that 15 look even bigger than week 1 because week 2 had a higher TFP. It's totally misleading and most anyone will be confused by it.

None the less, I have to create prototypes of each to display in front of the entire group so that we can actively discuss the pros and cons of each, then try to make a better decision with how to do it. If there is a way to perform what I'm looking to do, it'll make their other ideas/notions easier to mock up later.

Mugsley wrote: »

I think you all just wanted a reason to show off your Excel skills you've been hiding from the public.

I hate charts. I love the number crunching and writing macros to do cool shit, but the charts are for impressing the higher ups, and I find little to no enjoyment in that. Unfortunately, those same people could give two shits about a nested INDEX MATCH that I'm super proud of.

Le_Goat

Thanks for all of the feedback thus far. I tried to stand firm about this idea being misleading, but part of my role is to facilitate, not decide on my own. That being said, I'm trying my damnedest to figure this out. What you've all said has made me feel better about what I thought of this specific chart idea.

tsmvengy

Le_Goat wrote: »

Thanks for all of the feedback thus far. I tried to stand firm about this idea being misleading, but part of my role is to facilitate, not decide on my own. That being said, I'm trying my damnedest to figure this out. What you've all said has made me feel better about what I thought of this specific chart idea.

Elvenshae's first example is how you get to what you want to do. You have to create a table that figures out each ticket type percentage for each time period. Then multiply that by the average time to 1st response for that period and create a stacked bar chart from that result.

I would do the chart each way - the way that's been requested, and the way that shows total requests (my 1st example) and present them both. Explain the advantages or disadvantages of each.

schuss

What's the end goal of the metrics? Metrics are basically advanced number tracking.
Is your goal to standardize around avg. ticket times and measure effectiveness? If you're designing the visuals before the metrics, you've got the cart before the horse.

Elvenshae

Le_Goat wrote: »

I fully agree that it doesn't make much sense to do it this way. The head of our team really wanted to see something like what I'm asking for in this, but to me it falsely makes some items look like more than other items.

Ooooooooooooohhh.

It all makes total sense to me, now. In which case, yeah - the first one will do yah.

Mugsley wrote: »

I think you all just wanted a reason to show off your Excel skills you've been hiding from the public.

It is so rare that my day job is actually relevant to these boards.

schuss wrote: »

What's the end goal of the metrics? Metrics are basically advanced number tracking.
Is your goal to standardize around avg. ticket times and measure effectiveness? If you're designing the visuals before the metrics, you've got the cart before the horse.

Yes, this.

One of the key things to remember is that once you start tracking numbers and tying things to them (promotions, pay, bonuses, generic kudos, etc.), people are going to work to optimize those numbers. So be werry, werry careful about which numbers you decide to emphasize.

Le_Goat

schuss wrote: »

What's the end goal of the metrics? Metrics are basically advanced number tracking.
Is your goal to standardize around avg. ticket times and measure effectiveness? If you're designing the visuals before the metrics, you've got the cart before the horse.

What we've been doing before is nothing more than a glorified report card that shouts "Hey, look how many tickets we received for all of our platforms." I came into the job and inherited this task. Since then, I've been morphing it to include more data with minimal extra effort required by the rest of the team. We review it every week and it does nothing but show the typical ebb and flow of support. We can't do anything with it. There were discussions and a few arguments over why we even do this, especially since half the team has to manually track this shit into an workbook. That information is only good for headcount, and that's only once maybe twice a year. We can't improve anything we do with that crap.

The end game is to have metrics which show us how we are performing and use those as a way to improve our performance. If you are familiar with the Lean system, what we use at my company is very similar to that. We started out by asking "What do we do?" and sorted them into 4 major work-type categories. Then we discussed different types of metric categories. From there, we put those categories into 3 major metrics buckets, then placed those buckets under the major work-type categories (many-to-many) and chose one of those categories to focus on based upon what would have the most impact to performance while also aligning to our OU's objectives. After that, we discuss "What does good look like?" for that work-type category, followed by "What does bad look like?" for each one of those. All of this was discovery leading to what we need to see in order to know how we can create performance driven metrics, which leads to where we are now.

Elvenshae wrote: »

One of the key things to remember is that once you start tracking numbers and tying things to them (promotions, pay, bonuses, generic kudos, etc.), people are going to work to optimize those numbers. So be werry, werry careful about which numbers you decide to emphasize.

We've had trial and a ton of errors with this in previous projects. Someone wanted numbers now, so I gave them what I have with the caveat that they weren't a full time period, but the guy ran with them. A few months later, we're reviewing things and something seemed way off. When we were questioned about it and I broke it all down, it turned out that the guy rounded my percentages up, causing a snowball effect. To make matters worse, when I used a full time period instead of the numbers I was originally told to give which weren't a full time period, it made the end number reported look even worse. It was a massive headache that I don't intend to ever repeat again. No single person was at fault, but it was a prime example of why things need to be accurate and not tied to other things (like you mentioned), especially money.

Mugsley

Yeah, I work for [non-specified government agency] and we are *still* going through Lean/Six Sigma 12 years later.

If it makes you feel any better, I've just been asked to help explain -- for a third time -- why a design decision was made (before I took on this responsibility). Why am I being asked again? Because the Program changed management (and someone in the program office is putting bugs in management's ear that this doesn't work). It's getting ridiculous.

schuss

Ok - so do you have what good and bad looks like? That should be included in your display here. Ideally, you have 3 categories for each ticket - better than spec, neutral, worse than spec. The above is just stick counting and does nothing in terms of "how are we doing?". It's not actionable. From a high level, you need - what was the goal, what was the actual, what's the difference or delta? That generates action, as either it's "awesome!" or "oh god!" or "This one category is a problem, let's figure that out".
You need a performance standard line or some comparison to expectations. Also generally you should use medians and not averages, as the outlier effect totally fucks your numbers, especially with response time as it can only be dragged up.

Le_Goat

schuss wrote: »

Ok - so do you have what good and bad looks like? That should be included in your display here. Ideally, you have 3 categories for each ticket - better than spec, neutral, worse than spec. The above is just stick counting and does nothing in terms of "how are we doing?". It's not actionable. From a high level, you need - what was the goal, what was the actual, what's the difference or delta? That generates action, as either it's "awesome!" or "oh god!" or "This one category is a problem, let's figure that out".
You need a performance standard line or some comparison to expectations. Also generally you should use medians and not averages, as the outlier effect totally fucks your numbers, especially with response time as it can only be dragged up.

Good point on the median. I should probably aim for that. Thanks.

In our last meeting, we began talking down our SLA (normally 8 business hours) and set an internal TFP to 4 hours. It was the point where everyone in the room got uncomfortable, so that's what our goal is. We then said any week that goes over 8 hours once will require problem solving. If we go over 4 hours 3 times within a 4 week period, we'll need to problem solve for that. Any time we go under 4 hours, we look at it to determine what happened and how can we make that become a more common occurrence. There are more details to iron our, but that is the gist.

I plan to have two lines on the final chart which display that as a visual guidance.

schuss

Le_Goat wrote: »

schuss wrote: »

Ok - so do you have what good and bad looks like? That should be included in your display here. Ideally, you have 3 categories for each ticket - better than spec, neutral, worse than spec. The above is just stick counting and does nothing in terms of "how are we doing?". It's not actionable. From a high level, you need - what was the goal, what was the actual, what's the difference or delta? That generates action, as either it's "awesome!" or "oh god!" or "This one category is a problem, let's figure that out".
You need a performance standard line or some comparison to expectations. Also generally you should use medians and not averages, as the outlier effect totally fucks your numbers, especially with response time as it can only be dragged up.

Good point on the median. I should probably aim for that. Thanks.

In our last meeting, we began talking down our SLA (normally 8 business hours) and set an internal TFP to 4 hours. It was the point where everyone in the room got uncomfortable, so that's what our goal is. We then said any week that goes over 8 hours once will require problem solving. If we go over 4 hours 3 times within a 4 week period, we'll need to problem solve for that. Any time we go under 4 hours, we look at it to determine what happened and how can we make that become a more common occurrence. There are more details to iron our, but that is the gist.

I plan to have two lines on the final chart which display that as a visual guidance.

If you want to talk further, I can give some more ideas if you have some more info, as this stuff is what I used to do for a job and I'd be happy to help create good metrics, as the only thing worse than no metrics are bad metrics.

Le_Goat

schuss wrote: »

Le_Goat wrote: »

schuss wrote: »

Ok - so do you have what good and bad looks like? That should be included in your display here. Ideally, you have 3 categories for each ticket - better than spec, neutral, worse than spec. The above is just stick counting and does nothing in terms of "how are we doing?". It's not actionable. From a high level, you need - what was the goal, what was the actual, what's the difference or delta? That generates action, as either it's "awesome!" or "oh god!" or "This one category is a problem, let's figure that out".
You need a performance standard line or some comparison to expectations. Also generally you should use medians and not averages, as the outlier effect totally fucks your numbers, especially with response time as it can only be dragged up.

Good point on the median. I should probably aim for that. Thanks.

In our last meeting, we began talking down our SLA (normally 8 business hours) and set an internal TFP to 4 hours. It was the point where everyone in the room got uncomfortable, so that's what our goal is. We then said any week that goes over 8 hours once will require problem solving. If we go over 4 hours 3 times within a 4 week period, we'll need to problem solve for that. Any time we go under 4 hours, we look at it to determine what happened and how can we make that become a more common occurrence. There are more details to iron our, but that is the gist.

I plan to have two lines on the final chart which display that as a visual guidance.

If you want to talk further, I can give some more ideas if you have some more info, as this stuff is what I used to do for a job and I'd be happy to help create good metrics, as the only thing worse than no metrics are bad metrics.

That would be awesome. We've had to cancel tomorrow's meeting because I'm sick to the point where they gave me steroids, but we're meeting again next Wednesday. I'll post more then.