How to add a subtotal row to sum values belonging to specific categories
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello -
I am trying to replicate an excel summary table showing a row with subtotal for the categories above it. Table 1 is how I currently have the data formatted and Table 2 is how I want it to ultimately look like. How would I achieve adding a subtotal row in alteryx?
Thanks much!
Solved! Go to Solution.
- Labels:
- Input
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @JoRao
There are many ways to tackle this, but I would take the approach of using a "grouping category", Summarize tool, and Union tool set to a specfic output order. Let us know if the attached workflow works for you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @JoRao, you can also cross tab each of your data type (actual, budget, variance), calculate the sum of Printers, Furniture and Paper Total, then transpose them, and finally join them together (as shown below and in the attached workflow).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you @ddiesel. This is very helpful. How would I modify the work flow if I have region on the first column and want the final output table to look like this:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks @wenjuanchen
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Ignore my question @ddiesel. I figured it can be done by adding Region to the summarize tool as well.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@JoRao glad it worked for you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello.
Thanks for the suggestion that you provided.
When I pass my data into the Table tool, how do I still keep the same order when some columns are not part of the grouped data in the Table tool?
Also, how do I remove 'Null' from the columns that don't have any data?
