Hello,
I am looking for help to utilize multi field / aggregation tools that perform calculations for selected rows across multiple columns.
Category | Grade | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
Medium | X | 85 | 90 | 104 | 102 | 109 | 100 | 110 | 103 | 101 | 95 | 96 | 102 |
Small | X | 79 | 92 | 106 | 98 | 99 | 93 | 111 | 112 | 110 | 106 | 98 | 96 |
Medium | A | 84 | 82 | 120 | 73 | 138 | 122 | 84 | 137 | 132 | 105 | 84 | 63 |
Medium | B | 102 | 96 | 106 | 86 | 95 | 93 | 84 | 83 | 140 | 123 | 109 | 108 |
Small | A | 87 | 87 | 63 | 90 | 110 | 95 | 110 | 120 | 115 | 110 | 106 | 95 |
Small | C | 75 | 80 | 100 | 114 | 115 | 113 | 115 | 106 | 97 | 87 | 88 | 102 |
In the table above, top 2 rows with grades X are the benchmark numbers of the 3 categories for 12 months. I want to average each grade (A, B, A and C) with the corresponding benchmark grade for each category. That is, there are total of 4 averages to be calculated for each month;
1 Medium-A and Medium-X
2 Medium-B and Medium-X
3 Small-A and Small-X
4 Small-C and Small-X
I found several solutions such as pairing them up using filters or record IDs then calculating average, and union but, my solutions are too manual for my actual data set that is way larger then shown above.
Your help is much appreciated!
Thank you
Solved! Go to Solution.
Can you provide a table or screenshot with the output that you need for the example you provided?
Thank you for taking time and providing a solution. This is a great solution, and it works perfectly. But I was actually looking for usage of multi field tool in select row operations to shorten the workflow. For example, when I pair say, Medium-X and Medium-A row wise, how to average the columns across.
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | ||
Medium | X | 85 | 90 | 104 | 102 | 109 | 100 | 110 | 103 | 101 | 95 | 96 | 102 |
Medium | A | 84 | 82 | 120 | 73 | 138 | 122 | 84 | 137 | 132 | 105 | 84 | 63 |
Thanks
Thank you for looking in to this. The output cold be as simple as shown below. Or even simpler with no string field changes. and the numeric field values to show averages.
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | ||
Medium | A_Avg | 84.5 | 86 | 112 | 87.5 | 123.5 | 111 | 97 | 120 | 116.5 | 100 | 90 | 82.5 |
Medium | B_Avg | 93.5 | 93 | 105 | 94 | 102 | 96.5 | 97 | 93 | 120.5 | 109 | 102.5 | 105 |
Small | A_Avg | 83 | 89.5 | 84.5 | 94 | 104.5 | 94 | 110.5 | 116 | 112.5 | 108 | 102 | 95.5 |
Small | C_Avg | 77 | 86 | 103 | 106 | 107 | 103 | 113 | 109 | 103.5 | 96.5 | 93 | 99 |
Thanks
Brilliant!
Thank you so much.