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_CountFelonies911Misdemeanors611Total1522 TABLE B GradeCategorySum_Count%_of_TotalFelonies91159.8Misdemeanors61140.2Total1522100

% of Total

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

Output

Preparation

Accepted answers

Kenda

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!

All comments

Kenda

Hey @JohnMaty!

I would recommend adding a Filter tool that says:

[GradeCategory] != "Total"

[Sum_Count]/[Source_Sum_Count]

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

johnmaty percent total.PNG

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:

Filter Table A for Total:
```
[GradeCategory] = "Total"
```
I would likely use the SAMPLE tool and take the LAST 1 records.
Join that record to the original data so that every record contains a Right_Sum_Count field
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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703