Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Cascading Sums Across Rows Based on Field Variable

Mark3000
6 - Meteoroid

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

 

OrgDivisionTeamTeam MemberTeam Member Sales
NorthNorth CentralElmAndrew$180
WestDowntownBirchAnn$1,240
NorthNorth CentralMapleErin$600
WestUptownWillowGregory$230
NorthNorth CentralOakJill$500
NorthNorth CentralOakKevin$320
WestUptownHickoryOlaf$550
NorthNorthwestPineOpen$0
WestUptownWillowRobin$1,500
NorthNorthwestPineSatish$465

 

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

 

Team MemberTeam Member SalesTeamTeam SalesDivisionDivision SalesOrgOrg Sales
Robin$1,500Willow$1,730Uptown$2,280West$3,520
Gregory$230Willow$1,730Uptown$2,280West$3,520
Olaf$550Hickory$550Uptown$2,280West$3,520
Ann$1,240Birch$1,240Downtown$1,240West$3,520
Jill$500Oak$820North Central$1,600North$2,065
Kevin$320Oak$820North Central$1,600North$2,065
Erin$600Maple$600North Central$1,600North$2,065
Andrew$180Elm$180North Central$1,600North$2,065
Satish$465Pine$465Northwest$465North$2,065
Open$0Pine$465Northwest$465North$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.

2 REPLIES 2
LordNeilLord
15 - Aurora

Hey @Mark3000,

 

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

 

Cascading Sales.PNG

Mark3000
6 - Meteoroid

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

Labels