Calculations using calculated data in adjacent columns with different context

I have some data in a universe that I need to do some strange aggregations on in a web intelligence report. I'm stuck on the second and third aggregations after using a cross table to get the first aggregation. Hopefully this isn't a duplicate question, apologies if so; I've googled for hours.

Here's the general process I need to do:

1. I need to find the sum of all [Hours] for each [Part] in each [Machine] in each [MonthYear] and this is the vaue that gets put in the [Month Sum] column (let's call this step 1)
2. I need to take the mean of (step 1) for each [Part] in each [Machine] (i.e. average of each [sum of all operating hours for each part in each machine in each month-year] for each part in each machine). This gets put in the [Average of Month Sums] column (let's call this step 2)
3. Lastly I need to do the same thing as (step 2), just this time the sample standard deviation instead of average (shouldn't be an issue if I can get step 2 to work, just copy+paste and change the aggregation function). (step 3)

I have some sample data and I've gone through the steps as best I could in Excel. I can post .csv sample data as well if needed. I'm not sure how comprehensive this dummy data is, I'm unable to post real data but I believe it is sufficient.

I'm able to get (step 1) working with a cross table that I then convert to a vertical table. But when I try to do (step 2) by inserting a column in the vertical table using something like

=Average([Month Sum]) In ([Machine]; [Part])

it doesn't give the correct values. I've gone through the real report and checked the values on my calculator by showing a subset via the filter bar and I've double checked the data by downloading the .csv report and running it through a script I wrote in another language.

The excel sheet in the image is formatted to be easy-ish to read. I don't need the actual report to turn out that way, I just need a way to get the aggregation steps done in SAP Web Intel with correct values. It can use multiple reports if needed but the fewer reports the better. In the end the reports will be downloaded as .csv or .txt files.

The [Machine Part] column doesn't really exist in the report or data, I just put it in the excel sheet to give some more detail (Machine A has parts a and b; A.a != B.a; etc.). You can completely ignore it though if it is confusing.

Sample Data and general process I need to do:

Excel w/ formulas shown: