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

% of Total

JohnMaty
9 - Comet

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

GradeCategorySum_Count
Felonies911
Misdemeanors611
Total1522

 

TABLE B

GradeCategorySum_Count%_of_Total
Felonies91159.8
Misdemeanors61140.2
Total1522100
5 REPLIES 5
Kenda
16 - Nebula
16 - Nebula

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.

 

johnmaty percent total.PNG

 

Hope this helps!

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
JohnMaty
9 - Comet

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!

Labels