Unexpected total results

[dukane24]

Hello,

I am having a frustrating experience with a pivot table, specifically the totals, and I was hoping someone could help me to get it to work as expected.

Basically I've got a pivot table set up that looks at work types for a number of people, totals their inventory, and determines how long the work will take them based on a time standard.  So there are three expressions:

Inventory - The Sum of the work

Standard - Sum of Time taken to do the work divided by number of transactions (basically the average time taken to do the work)

Total time - Inventory times Standard

The resulting table looks like the one below.  What's confusing me is the total column - the total reflects the sum of the inventory, and the standard total is close to an average, but the total time is in some cases nowhere need a total of the times across the various items types, in this case Type 1 and Type 2.  In the actual app, I have the totals for the Inventory and Standard hidden, so the user will only see the total for the Total Time.  What I'm looking for is for the total for the Total Time to be an actual total of the time for all of the item types.

I appreciate any help you can offer in advance.   

Name		Type 1	Type 2	Total
Tom	Inventory	1820	129	1949
	Standard	2.67	1.71	2.16
	Total Time	4851.95	220.7	4217.16
Jane	Inventory	269	18	287
	Standard	1.79	1	1.14
	Total Time	481.92	17.99	327.38
Bob	Inventory	484	22	506
	Standard	3.18	1.22	2.35
	Total Time	1539.7	26.87	1191.55

[vishsaggi]

can you share a sample to look into? What is your actual expression for Total time? May be try like

= Sum(Inventory) * Sum(Standard)

[marcus_sommer · Accepted solution]

You will need an aggr-function for it. Probably something like:

sum(aggr(sum([Total Time]), Name, Type))

- Marcus

[dukane24]

Thank you Marcus! This did just what I needed.

[dukane24]

Thank you for replying - this is very close to what I was using - it was closer to Sum(Inventory) * Avg(Process_time).  At a granular level the results were as expected, but the total was more of the sum of the inventory times the average of the process times for the various work item types once that dimension was added.  The users are expecting to see the sum of the granular (broken down by the dimension) Total Time.

[vishsaggi]

Got it. Glad it worked for you.