% of Total

[JohnMaty]

This is maddening.  It seems too simple but I am having a tough time with it.  I have TABLE A.  I need to add a % of Total Field to it.  TABLE B.  How do I do it?

TABLE A

GradeCategory	Sum_Count
Felonies	911
Misdemeanors	611
Total	1522

TABLE B

GradeCategory	Sum_Count	%_of_Total
Felonies	911	59.8
Misdemeanors	611	40.2
Total	1522	100

[Kenda · Accepted solution]

Hey @JohnMaty!

I would recommend adding a Filter tool that says:

[GradeCategory] != "Total"

Then, use an Append Fields tool with the T from the Filter feeding into the T on the Append Fields and the F from the Filter going into the S on the Append Fields. Lastly, add a Formula to create a new field that says:

[Sum_Count]/[Source_Sum_Count]

Then you can multiply by 100 if you'd like.

Hope this helps!

[MarqueeCrew]

@JohnMaty,

Let's think about this as an Excel problem first.  Cell C2 would be:  Sumif(A:A,"Total",B:B).  This would look for the "Total" value in column A and would return the value in column B of the row matching that value.

Translating that to Alteryx:

1. Filter Table A for Total:
   

   [GradeCategory] = "Total"

   I would likely use the SAMPLE tool and take the LAST 1 records.
2. Join that record to the original data so that every record contains a Right_Sum_Count field
3. Use a Formula tool:
   

   [Sum_Count] / [Right_Sum_Count]

Cheers,

Mark

P.S.  I heard a beep and think that @Kenda might be with me on this one too.

[MarqueeCrew]

@JohnMaty,

I was RIGHT!  @Kenda is with me.  She uses an Append Fields tool.  That is very appropriate and is the tool to use.  She joins the data from the T and F anchors, that's where we differ.  I want to keep the Total column so that it appears as 100%.  So take the input for the filter and connect that to the S anchor.

The best of both worlds.

Cheers,

Mark

[JohnMaty]

Interesting.  It worked.  I have never used this tool before and I am unsure what it actually did but I will research it further.  I would like to understand it rather than just accept it!  I am sure this will be very handy in the future!   Thanks again for the help!

[Kenda]

Try this article by @patrick_digan

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Append-Fields/ta-p/87489