Combo pivot table graph?

I have a script which collects performance metrics every 10 seconds and aggregates the data per hour into a CSV file. This is then imported into Excel, and I’m building some daily graphs for visualising the data.
The logged data is broken down into three categories, Slow, Medium and Fast, and shows the number of times each were triggered per hour, as well as the average, minimum and maximum raw speed values for each category, e.g:

Date/Time	Category	Count	Avg	Min	Max
2018-09-10T00:00:00	Slow	216	15	5	22
2018-09-10T00:00:00	Medium	144	45	34	64
2018-09-10T01:00:00	Medium	162	50	36	60
2018-09-10T01:00:00	Fast	198	80	75	99

(Each hour can have a different number of rows associated with it)

Currently I have added a new column which parses the ISO-8601 timestamp as a native date/time, use a pivot table aggregating the counts of the three categories per hour, and display the results as a stacked area graph which is working nicely.
What I’m looking for is a way of being able to overlay a line graph of the combined average speed per hour, using a Combo graph and the secondary axis. Bonus points for visualising the minimum and maximum datapoints using something like a Stock graph.

Is this possible, or is there a better way of accomplishing what I'm after?
Thanks,


Mike

Hi dflak,

The CSV file is generated daily, and currently a new Excel spreadsheet is built each day from the newly imported data.
Once the process is better established this part will become automated, and at that point Tables sound like a good option.

Attached is a sample 2-day dataset, with the the pivot table and speed category graph on the second sheet.

I suspect there needs to be an intermediary data table which collates the data into a more regular structure, but unsure how to best accomplish it.

Thanks,


Mike

Thanks for the suggestions, using tables for the source data especially in conjunction with pivot tables looks to have some nice benefits.
I hadn't considered putting the data update within the report itself, interesting solution.

One question though, what was the "Whole Date" field for? Was is just to give the slicer some data to work with? Is it beneficial to use a new column rather than the Days grouping of the original date field?

I've been making a little more progress on the speed graph (attached) by adding an additional column to the source data which computes the cumulative speed per category per hour, then used a computed field in a second pivot table which sums these and takes the overall average per hour. I'm sure there's probably a way of performing this computation entirely in the pivot table without adding the additional field, but my totals were coming out way off using that approach.

This is near what I was originally after, although in two separate graphs which is a little cumbersome but workable.

Also, probably a simple question, but is there a way of retrieving the Slicer's currently selected value(s) in a formula, specifically to show the currently selected day of the week? For example it would be nice to dynamically set a chart title based on the currently selected day. I've seen some examples online using Cube*() functions to do so, but the docs for these don't seem to make much sense for a newbie.

Thanks,


Mike