I have data in row/column format that's nested. I need to get an Average Task Duration of each Group, Channel and Task Segment. I've tried several different separate and join options in workflows but every single one brings them back nested but the Group level is just summing or averaging each shown value. For example if I had Group 1 Channels 1,2,3 with Task Segments A,B,C the group level is showing 3 instead of 1 whether I pick average or sum using Summarize or Crosstab tools.
What I Have
GroupChannelTask SegmentAvg_Task Duration
| 1 | Red | Circle | 3.42 |
| 1 | Red | Square | 12.03 |
| 1 | Red | Triangle | 1.64 |
| 1 | Red | Rectangle | 1.57 |
| 1 | Blue | Circle | 3.27 |
| 1 | Blue | Square | 7 |
| 1 | Blue | Triangle | 2.28 |
| 1 | Blue | Rectangle | 2.66 |
| 1 | Green | Circle | 1 |
| 1 | Green | Square | 1 |
| 1 | Green | Triangle | 0.01 |
| 2 | Yellow | Circle | 5.18 |
| 2 | Yellow | Square | 13.97 |
| 2 | Yellow | Triangle | 3 |
| 2 | Yellow | Rectangle | 1.86 |
| 2 | Orange | Circle | 3.43 |
| 2 | Orange | Square | 10.89 |
| 2 | Orange | Triangle | 1.93 |
| 2 | Orange | Rectangle | 3.05 |
WHAT I NEED:
| | Circle | Rectangle | Square | Triangle | Grand Total |
| 1 | 2.56 | 2.12 | 6.68 | 1.31 | 3.26 |
| Blue | 3.27 | 2.66 | 7.00 | 2.28 | 3.80 |
| Green | 1.00 | 0.00 | 1.00 | 0.01 | 0.67 |
| Red | 3.42 | 1.57 | 12.03 | 1.64 | 4.67 |
| 2 | 4.31 | 2.46 | 12.43 | 2.47 | 5.41 |
| Orange | 3.43 | 3.05 | 10.89 | 1.93 | 4.83 |
| Yellow | 5.18 | 1.86 | 13.97 | 3.00 | 6.00 |
| Grand Total | 3.26 | 2.29 | 8.98 | 1.77 | 4.17 |
