Group Header Value & Calculated Column Value

[gurltech]

I have a report where I am calculating average users' average hours per week.  In my grouping header, I want to show the sum average of the hours over a 6 week period (so total hours over the 6 week period divided by 6) & then in the subgroup headers (each row) I want show the average hours per week (total hours for the one week divided by count of the hours for that week). 

In my attached screenshot:

• Main group header: Hours 232, Avg Hours / Wk 38.67 > This is calculated by 232 / 6 = 38.67
• Subgroup header: Hours 40, Avg Hours / Wk 6.67 > This is where I am running into the problem.  I need to divide by the count.  In the screenshot, the count is 11, so to get the weekly average is should be 40/11= 3.64.

There is a copy of the code in the screenshot.  I'm not sure what I need to divide by to get the weekly average because my row count will change depending on the number of entries per week.  Is this possible or is there a better way to do it?  Any help would be appreciated.  Thanks.

[skyehansen]

I might be missing something and hence stating the obvious -- you can and should take whatever text mode you need for averaging from the standard mode for average. I'm seeing in your screenshot you've chosen to use "SUM" which probably will result in your adding everything in that column.  I feel like maybe if you select average in the dropdown, the result will be that it knows to divide by the count, but math is not my strong suit, so I'd have to play around with it to see.

[gurltech]

I previously tried the average & it works great for the subgrouping headers.  The problem that I'm running into with that is with the main grouping header - value for that is incorrect for what I need.  It's the opposite in this scenario - the main group heading is off & the subgroup header is correct.  So in this case I figure out how to divide my main group hour total by 6 (232 / 6 = 38.67 in this example - in the attached screenshot with the average selected you can see that it shows 3 in the main grouping).  Hopefully that all makes sense.  Thanks for your reply!

[skyehansen]

I can't say I really understand, but do agree that you can't have two different aggregations on the same column. We usually set up two columns, for example, one with a SUM aggregation and the other with an AVG aggregation and coach the users on which number to focus on. Not sure if that solves your particular problem but if not, maybe you can clarify.