cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## Cascading Sums Across Rows Based on Field Variable

Meteoroid

So, I'm trying to build out a table based upon a data set that looks like this:

 Org Division Team Team Member Team Member Sales North North Central Elm Andrew \$180 West Downtown Birch Ann \$1,240 North North Central Maple Erin \$600 West Uptown Willow Gregory \$230 North North Central Oak Jill \$500 North North Central Oak Kevin \$320 West Uptown Hickory Olaf \$550 North Northwest Pine Open \$0 West Uptown Willow Robin \$1,500 North Northwest Pine Satish \$465

With the goal of getting to a table that looks like this:

 Team Member Team Member Sales Team Team Sales Division Division Sales Org Org Sales Robin \$1,500 Willow \$1,730 Uptown \$2,280 West \$3,520 Gregory \$230 Willow \$1,730 Uptown \$2,280 West \$3,520 Olaf \$550 Hickory \$550 Uptown \$2,280 West \$3,520 Ann \$1,240 Birch \$1,240 Downtown \$1,240 West \$3,520 Jill \$500 Oak \$820 North Central \$1,600 North \$2,065 Kevin \$320 Oak \$820 North Central \$1,600 North \$2,065 Erin \$600 Maple \$600 North Central \$1,600 North \$2,065 Andrew \$180 Elm \$180 North Central \$1,600 North \$2,065 Satish \$465 Pine \$465 Northwest \$465 North \$2,065 Open \$0 Pine \$465 Northwest \$465 North \$2,065

The sorting and order aren't a problem, but filling in some of the values is slowly driving me crazy. The key thing we're trying to get to is a new sum for each higher-level category (Team / Division / Org) on a row-level basis. For example, the sales for Team Willow add up to \$1730 (with the value shown on two rows). That team is part of Division Uptown, the sales for which add up to \$2280 (the value shown on three rows). That division belongs to Org West (four rows), sales for which add up to \$3520. Note the number of rows for each category can vary.

I've been able to do up to two levels with the summary tool but have been stuck after that. Doing a cascading series of joins doesn't work as in the real data set I'm dealing with hundreds of categories for the Team, Division and Org fields. I'd appreciate any thoughts on how to attack a report like this.

Alteryx Certified Partner

Hey @Mark3000,

You need to do multiple summarizes and joins to get the correct sums at each level:

Meteoroid

@LordNeilLord, that worked perfectly (and was so much simpler than the approach I was trying to take). Thank you!

Labels