Grand Total and Percentages Question

[missteran]

Hi Alteryx Community -- 

I've been stuck on this for a few hours so figured I'd reach out for some help. Seems simple enough to do, I attached the alteryx text input file for reference. but essentially what i'm trying to get my output to do is layout Top 3 Instruments, by Grand Total. If others fall below the top 3 to just name then as All Other, and then also a column to provide % of Amount (which is Amount divided by Grand Total of Amount), and % of Utilization (which is Utilization divided by Amount). Can someone help?

Initial data set I worked with (as an example):

Type	Instrument	Amount	Utilized Amount
Commodities	Standby Performance	35	35
Commodities	Loans	100	100
Commodities	Loans	200	190
Commodities	Loans	250	240
Commodities	GAAP Loans	200	200
Commodities	FVO Loans	100	100
Commodities	LLE	150	150
Commodities	LLE	150	150
Commodities	FVO Loans	100	100
Commodities	GAAP Loans	50	45
Commodities	GAAP Loans	80	80
Commodities	Standby ABC	25	25
Commodities	Standby XYZ	30	10
Commodities	Standby XYZ	30	10

Expected Output I'm looking to get:

Top 3 by Instrument	Amount	% of Amount	Utilized Amount	% of Utilization
Loans	550	37%	530	96%
GAAP Loans	330	22%	325	98%
LLE	300	20%	300	100%
All Other	320	21%	280	88%
Grand Total	1,500	100%	1,435	96%

[Felipe_Ribeir0 · Accepted solution]

Hi @missteran 

One way of doing this

[Armon24]

Here is an example of doing this dynamically. This workflow can be even more streamlined by combining some steps. 

[binuacs]

@missteran One way of doing this

[Qiu · Accepted solution]

@missteran 
I like to use the combination of Transpose and Cross Table for the case like this.

[missteran]

Thank you all - the solutions provided really helped - appreciate it big time!!