Adding Subtotals to output after going through Summarize and Crosstab tools.

[knobsdog]

I need to take data I've run through a summarize and crosstab to get nested rows but on those rows I need a summary subtotal going across the row.  I've attached an example workflow with fake data since my data is sensitive.  It matches the kind of thing I'm looking to do perfectly down to the columns/rows nested summary etc. 

Also is there a way to show the items without an Update Date, showing as null, to the bottom of each section, ie CS, DiPC, etc?

Can anyone figure out how to do this?

[phottovy]

I didn't quite get the same numbers as your example but try this workflow. I added several tools directly after your first summarize tool.

[phottovy · Accepted solution]

@knobsdogI just reread your original description. Here are two slightly modified workflows. Hopefully one of them gets you close to your desired table but let me know if I am still not fully understanding your question.

[knobsdog]

I will take a look.  Thank you for the quick response.

[knobsdog]

V2 is exactly what I was looking to do.  Thank you so much for your help.

[knobsdog]

Now that I've adjusted your workflow to my actual data I'm noticing there's no grand total.  There's a subtotal for each division but I can't seem to get it to show the complete total for all divisions without it showing twice.  Any ideas?

[phottovy]

You can easily add a grand total by using a Summarize tool followed by a Union tool. I added the changes to the attached workflow.

[knobsdog]

Sorry for the delayed response.  I just looked through your solution but it's not quite right.  It's totaling everything in the column including the subtotals so it's actually double counting everything.  This is so close to what I'm looking for but I need to basically just total either the column amounts minus the subtotals or just total the subtotals without the rest of the items in the column.

[phottovy · Accepted solution]

@knobsdogYou are correct, I didn't account for that. There are two quick ways I can think of to fix this. First is add a filter before I calculate the grand total (V2 attached) or calculate the grand total in the same step that I originally calculated the subtotals (V3 attached)

[knobsdog]

V3 works great, thank you so much for your help and quick reply.