I couldn't connect to the server that your attached workbook uses, so I may not have understood your question correctly.
If I did, I think a level-of-detail (LOD) expression would help. Define a calculated field like this:
This will make sure aggregation is always happening per second, regardless of the dimensions in your viz. You can have a per-hour dimension in your viz, and still get aggregation per second if you drag the calculated value defined above.
I'm attaching a sample viz on a simple text file I created. I have 2 sheets in it - one aggregates the LOD defined above (Avg Per Second), the other - just the Value column according to the dimensions in the viz (Avg Per Hour). You will see how they are different.
Hope this helps with your problem. If you have questions, please continue the thread - I'd be happy to help.
AvgPerSecond.twbx 5.7 KB
Thanks very much! I ended up having to alter the SQL query that provided the data, but what you said was part of the solution.