Subtotals by summing averages

[shasta_man]

C'mon Mr Excel. Thanks for the help.

I'm looking to simply subtotal, by sum, average values in my pivot table. I want to compare the sum of average runtimes. It takes a lot of time to construct this otherwise.

Essentially, I have a group of test data. Each tests consists of multiple operations. Each operation is a runtime. Operations are then grouped into hierarchical groups with operations grouped into applications which are grouped into scenarios which are grouped into tests, summing into one big total. Each test is run a number of iterations on a system and are grouped into a project. There are multiple systems running multiple projects. Ton of data.

My goal is to compare the operations times of each system, but subtotalled at each level of the hierarchy. To compare the total time for each system for all the operations in an application, then a scenario, then a test, then a project.

Creating a pivot table with operations as rows and systems are columns with runtime value set to average subtotals each level of the hierarchy by averaging the averages of the operations below it. Instead, I need to find the average time for the operations, but then sum those at each level of subtotal in the hierarchy.

I'm currently putting formulas next to the pivot table to sum the rows in the pivot table which is very labor intensive.

 

[dermie_72]

which version of excel are you using?
I'm pretty sure that you can add the subtotals, of specific fields into the pivot.
You may need to put a sample of your spreadsheet up. Refer to the "welcome to the forum post" on how to add one.

 

[shasta_man]

I'm using Excel 2010.

Here is an example:

Since webpages can't handle whitespace, it's wiped out any attempt at columnizing this data (attaching spreadsheet doesn't seem to be working), so I'll describe.

These are the data records, with headings.
FIrst record is iteration 1, operationID 1, App1, Scenario 1, runtime of 83
Second record is iteration 1, operationID 2, App1, Scenario 1, runtime of 47
Continuing down until getting to iteration 2 where the whole thing repeats, then iteration 3.

Iteration OperationID Application Scenario Name Runtime
1 1 App1 Scenario 1 83
1 2 App1 Scenario 1 47
1 3 App2 Scenario 1 81
1 4 App2 Scenario 1 125
1 5 App3 Scenario 1 95
1 6 App3 Scenario 1 75
2 1 App1 Scenario 1 113
2 2 App1 Scenario 1 118
2 3 App2 Scenario 1 77
2 4 App2 Scenario 1 81
2 5 App3 Scenario 1 27
2 6 App3 Scenario 1 53
3 1 App1 Scenario 1 79
3 2 App1 Scenario 1 58
3 3 App2 Scenario 1 102
3 4 App2 Scenario 1 63
3 5 App3 Scenario 1 47
3 6 App3 Scenario 1 80

Setting Values formula to show averages, shows the average time for each operation from the 3 iterations above:

OperationID App Scenario Runtime
1 App1 Scenario 1 91.7
2 App1 Scenario 1 74.3
3 App2 Scenario 1 86.7
4 App2 Scenario 1 89.7
5 App3 Scenario 1 56.3
6 App3 Scenario 1 69.3


Now I want to subtotal by App (sum all of the operations for a given app).

The correct answer for me (the sum of the averages of each operation) is:
App1 Scenario 1 166 - 91.7+74.3
App2 Scenario 1 176.3 86.7+89.7
App3 Scenario 1 125.7 56.3+69.3

However, the pivot table, since I set the Value formula to use average to get the average operation times, correspondingly subtotals by averaging the averages showing this:
App1 Scenario 1 83
App2 Scenario 1 88.2
App3 Scenario 1 62.8

I need to use average formula to get average operation time but then add those times for the subtotals. How can I set it to sum for subtotals while averaging at the op level? I'm setting rows to App, OperationID and Values to Average of Runtime. Setting to Values to Sum merely sums all iterations.

There must be some solution or different way of organzing the data to achieve this.

 

[dermie_72]

I would look at sumif and sumifs then. That may provide you with the answer you are looking for.

When it comes to averages, it's (sumif + sumif + sumif)/however many

 

[shasta_man]

Thanks for your assistance.

I think you are saying to construct a table in Excel using SUMIFS and AVERAGEIFS. I've been doing that but prefabbing tables that way is just not a good solution because it takes a long time to construct and has to be custom constructed for whatever you are looking at. Any changes to the criteria and you have to go though a long process of changing all of the criteria. Another problem is that prefabbed tables don't show you what's in the data. They show you what you ask for. That's not the same thing. The benefit of the pivot table is that it shows you a table based on the data, so everything is included so you can see bad data too.

I'm thinking the easiest thing, though not at all preferred, is to generate a database of averages. I can then use that normally with sum subtotals. That's not a good solution either because you introduce more likelyhood of errors when not connected directly to your data.

I'm amazed that this isn't possible and that more people aren't impacted by this. I'm thinking it's because most people don't use pivot tables. They are amazingly powerful that I can make a quick summary that would take hours to construct with sumif and averageif.

Anyone else have any thoughts on DAXMeasure formulas or reorganizing the data to be able to calculate this?

 

[Laurent C]

Have you tried the SUMX function? Something like:

[Average Operation Time] := AVERAGE( 'Data'[Runtime] )
[Sum of avrages] := SUMX( VALUES( 'YourDim' ) , [Average Operation Time] )

 

[shasta_man]

Thanks so much for the response, Laurent. I haven't tried SUMX. I've just scratched the surface of DAX Measures. I'll look into that.