Nested Output Using Averages But Averaging Totaling on List

[knobsdog]

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

[JagdeeshN · Accepted solution]

Hi @knobsdog ,

Please find attached a sample workflow which does the transformation you were looking for.

Do let me know if this works.

Best,

Jagdeesh Narayanan

[knobsdog]

I'll take a look, thank you for your quick response.

[knobsdog]

I had to adjust a couple things but your solution got me 95% there.  So thankful for your help, I really appreciate it.

[JagdeeshN]

@knobsdog 

Awesome!. Glad I could help.

[knobsdog]

Thanks again.

[JagdeeshN · Accepted solution]

@knobsdog 

Please check if the solution in the below thread helps your usecase:-

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Bolding-specific-text/td-p/334281

Best,

Jagdeesh Narayanan

[knobsdog]

I spoke too soon.  I figured out how to write a formula in the Basic Table tool that will bold and increase the font size based on the text in the column.

Thank you again for your help.

[JagdeeshN]

@knobsdog 

Awesome.  Can you share a sample formula here if you don't mind?.

I ask as that can be helpful to others.

Best,

Jagdeesh Narayanan

[knobsdog]

Good idea here's the steps:

In the Basic Table Tool:

Under Per Column Configuration Section at the bottom

1. Select field you want to adjust the font,
2. Click Column Rules > Create
3. Check Formula button
4. Click 3 dot menu and type formula: [_CurrentField_] IN ("1","2"..."n")
5. Below Formula box check Font box and pick bold, italic, font, etc
6. Check Font Size and pick font size
7. Click Close button