Subtotals on crosstabs

[gharding]

I have a crosstab that comes out nice.

I have three groupings a week number a name and then a date.  Values are grouped by a field which works out great.

However, I need to be able to provide a subtotal after each name changes, as well as have a total on the right.

Is there a way to do that?  

Thank you!

Updated note to include sample answer with the things I want in yellow highlight

[AGilbert · Accepted solution]

Here is a basic example of a subtotal by name and joined to each record of the original data. If you provide a sample workflow, and a mockup of the desired output, we can get you a better solution. 

[gharding]

Thanks!  However, i edited the post and left an attachment - the yellow highlight is what I am looking for.

Thanks

[binuacs]

@gharding What are the expected output results based on your input file?

[AGilbert]

 Try the attached to match your yellow markups. 

edited: updated attachment to include the grand total row and make subtotal row null values = 0. 

[gharding]

Agilbert - that was great. But still not enough.

The file was created as a crosstab, summarizing on a number and the columns may be less or more, depending on the file and the time of the month where more categories hit the report.  I am trying to send a non-proprietary data file that will give you an idea. Will come shortly.

[gharding]

I have attached the workflow I have, and the crosstab that I had set up which gave me the original before the yellow lines I wanted.  Hopefully you understand better now!

Thanks,

Gary

[AGilbert]

I connected my solution to your source data + cross tab tool. The output seems to match your expected format. The workflow should handle a variable number of rows and columns, but we can test it. Take a look at the attached output and let me know what isn't right. 

[gharding]

Thanks for everything @AGilbert as expected I forgot something.  Can you adjust this - if I added another field to the pull?  I added OFFC (col B).  I would try to replicate, but I am nowhere near that yet.

I appreciate the help.  After this - it will be accepted as solution

Gary

[AGilbert]

Happy to help. I updated each Transform (orange) tool to include the new field as either a group by or key field. Essentially, treat it the same as other row headers like Week, Name, and Date. 

Workflow and output attached. Let me know if something looks off or you need more explanation on how to keep it updated.