I'm looking for a process that can recreate the % column below. You can see that there are multiple rows of which the total calculation is based on within the same field (total assets, total liabilities), and this is where I'm struggling in carrying this over to alteryx. I'm wondering how I can replicate this in a large WF with many more totals and columns with similar data. Please let me know if this isn't clear. Cash $ 4,3760.15% Loans 2,803,47997.28% Allowance for loan losses (30,090)-1.04% Net loans 2,773,38996.24% Loans held for sale 5930.02% Accrued interest receivable 17,0690.59% Equity investments in other Farm Credit institutions 32,0741.11% Premises and equipment, net 16,4380.57% Other property owned 6220.02% Accounts receivable 34,5191.20% Other assets 2,8010.10% Total assets 2,881,880100% Liabilities Notes payable to Farm Credit Bank 2,181,49697.09% Accrued interest payable 5,9000.26% Patronage refunds payable 30,8361.37% Accounts payable 2,4200.11% Other liabilities 26,2781.17% Total liabilities 2,246,930100%

Multiple % sub-totals in one column

I'm looking for a process that can recreate the % column below. You can see that there are multiple rows of which the total calculation is based on within the same field (total assets, total liabilities), and this is where I'm struggling in carrying this over to alteryx. I'm wondering how I can replicate this in a large WF with many more totals and columns with similar data. Please let me know if this isn't clear.

Cash	$ 4,376	0.15%


Loans	2,803,479	97.28%
Allowance for loan losses	(30,090)	-1.04%

Net loans	2,773,389	96.24%

Loans held for sale	593	0.02%
Accrued interest receivable	17,069	0.59%
Equity investments in other Farm Credit institutions	32,074	1.11%
Premises and equipment, net	16,438	0.57%
Other property owned	622	0.02%
Accounts receivable	34,519	1.20%
Other assets	2,801	0.10%

Total assets	2,881,880	100%

Liabilities
Notes payable to Farm Credit Bank	2,181,496	97.09%
Accrued interest payable	5,900	0.26%
Patronage refunds payable	30,836	1.37%
Accounts payable	2,420	0.11%
Other liabilities	26,278	1.17%

Total liabilities	2,246,930	100%

Workflow

Accepted answers

ArtApa

Hi @BSmithdubz - You can do something like this:

I attached a sample workflow for your convenience.

sub-totalsAA.yxmd

All comments

PhilippK

Hi @BSmithdubz

I recommend the following:

1.) split your table into 2 tables: assets & liabilities

2.) crunch the numbers per table (e.g. % column)

3.) union the 2 updated tables back together

Let me know whether this helps you.

Best regards

Phil

ArtApa

Hi @BSmithdubz - You can do something like this:

I attached a sample workflow for your convenience.

sub-totalsAA.yxmd

BSmithdubz

This is great - thank you! My only concern now is that I have to replicate this for 60 or so fields. I think my best move may be to batch macro by unit (20 units) and then slightly edit the formula. Thanks again.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335