Show % Variance in Stacked Chart
I am creating a report that shows the response time of our fire department to incidents. More specifically I want to compare the response time of the first arriving unit to a benchmark. I have isolated the data set to an example that illustrates this. First thing I did was calculate the response times for all units per incident
I have circled the fastest response times for each incident for clarity. The next thing I did was to use the Aggregation -> Minimum on the response time which led to the following:
This is exactly what I would expect - far so good. Now I want to show the % of the incident responses by year and quarter (cols 1 and 2 above) that met our objective (6 minutes). Based on the above, you can see that 2/3 or 66% met the objective for Q1 of 2017 and 0/1 or 0% met the objective for Q4 of 2017. Poking around a bit, I see that I can use a Variance function in the charting area which I would think gives me the info I need. So I added a record counter to each record that would give me the total:
Next, I go to the chart area to create a vertical column chart. On the chart, I add "Quarter" to the horizontal axis, and create a Variance for the vertical axis. For my variance, I create two custom sets - the first being the total record count as follows:
Next I created a second set looking at just the incidents that had arrival times of greater than 6 minutes:
Finally I am using the "Display Variance as" option of "# Value".
This gives me the results that I am expecting - namely, 2 met the benchmark in Q1 and 0 did in Q4:
Now finally, I want to show this number as a %. In looking at the "Display Variance as" options, none of the formulas would give me the result I would expect. It should read 66% for Q1 and 0% for Q4. The closest option I think is % Percentage, but that formula ((v1-v2)/v2) for Q1 yields:
(3-1)/1. It would seem to me that the function should be ((v1-v2)/v1) which would give me: ((3-1)/3 = 66%. Any help would be appreicated - thanks!