Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
You will need an aggr-function for it. Probably something like:
sum(aggr(sum([Total Time]), Name, Type))
- Marcus
can you share a sample to look into? What is your actual expression for Total time? May be try like
= Sum(Inventory) * Sum(Standard)
You will need an aggr-function for it. Probably something like:
sum(aggr(sum([Total Time]), Name, Type))
- Marcus
Thank you Marcus! This did just what I needed.
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.
Got it. Glad it worked for you.