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

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?

Workflow Pic.png

Subtotal By Dimension and Extra Total Help Example.yxmd

Accepted answers

phottovy

@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.

Subtotal By Dimension and Extra Total Help Example.yxmd

Subtotal By Dimension and Extra Total Help Example V2.yxmd

phottovy

@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)

Subtotal By Dimension and Extra Total Help Example V2.yxmd

Subtotal By Dimension and Extra Total Help Example V3.yxmd

phottovy

@knobsdogThis should do the trick. I added another union tool after the sort tool and selected "Set a Specified Output Order" so the Grand Total will always be added to the bottom. As long as you don't sort again after, this should solve that issue.

Grand Total on Bottom Example.yxmd

All comments

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.

Subtotal By Dimension and Extra Total Help Example.yxmd

phottovy

Subtotal By Dimension and Extra Total Help Example.yxmd

Subtotal By Dimension and Extra Total Help Example V2.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703