Using Append Field Tool for Multiple Variables

[bwortham32]

Hello All,

I'll try to explain this without confusing everyone.  Initially, I had the data below in which I had to use the Append Fields tool to calculate a % of business field.  I had to use this tool because my calculation multiples each row's Quantity by the Total Quantity in order to get the percentage.  My summarization going into the Append Fields tool target is grouped by State and Discount Level and summed by Quantity.  My summarization going into the source is the Sum_Quantity.

State	Discount Level	% of Business	Quantity
OR	50	15%	180
OR	40	15%	180
OR	30	15%	180
OR	45	10%	120
OR	35	15%	180
OR	25	10%	120
OR	20	10%	120
OR	15	5%	60
OR	10	5%	60
OR	5	5%	60

I would like to add another level to this with a snapshot of the data below.  I want to include % of Business based upon one extra variable (City).  Before, the % of Business totals 100% based on the State.  I would like the 100% to be rolled up to the city level.  Any ideas on how to do this?  I tried grouping City into the Source section along with the Sum_Quantity of the Append tool, but it does not seem to work.  Any help is greatly appreciated.

State	City	Discount Level	% of Business	Quantity
OR	Portland	50	55%	165
OR	Portland	40	40%	120
OR	Portland	30	5%	15
OR	Eugene	45	20%	80
OR	Eugene	35	35%	140
OR	Eugene	25	25%	100
OR	Eugene	20	20%	80
OR	Salem	15	50%	250
OR	Salem	10	40%	200
OR	Salem	5	10%	50

[Joe_Mako · Accepted solution]

How about the attached?

It will Summarize your data to the State-City combination level getting the Sum of Quantity, Join that back in with your original data on those two dimensions, and a Formula to calculate the percentage.

[bwortham32]

Thanks, Joe.  Not sure why I tried to complicate it so much!