Percentage formula on columns

[Danah_mr]

Hi all,

I have the following Data and i need to Calculate the Material as % of DCC  row for each year (Total DCC/Total Material Cost) * 100) using Alteryx Designer. Can someone help me on how to apply this formula ?

Data:

Cost Element	2020	2021	2022	3 Yr Average
Total DCC	$164,406,097	$178,020,724	$187,171,952	$176,532,924
Total Material Cost	$14,757,805	$18,857,448	$22,690,419	$18,768,557

Desired Output:

Cost Element	2020	2021	2022	3 Yr Average
Total DCC	$164,406,097	$178,020,724	$187,171,952	$176,532,924
Total Material Cost	$14,757,805	$18,857,448	$22,690,419	$18,768,557
Material as % of DCC	9%	11%	12%	11%

Thanks!

[Qiu · Accepted solution]

@Danah_mr 
A quick sample can be something like this.

A lot of Tanspose and Cross Tab 😁

[Danah_mr]

thank you ! 

the Tanspose and Cross Tab is a magic  ✔️

[Danah_mr]

.

[Danah_mr]

@Qiu 

what about applying a formula with a total same as the example below?

 % of OT formula (A Total /(A Total + B Total)) *100 and (B Total /(A Total + B Total)) *100

Cost Element	Actual	Plan	% Var	% of OT
A Total	19,693,573	19,759,044	-0.33%	99%
1) A	13,416,508	14,438,782	-7.08%
2) A	6,277,065	5,320,263	17.98%
B Total	146,777	0		1%
1) A	2,163	0
2) A	144,614	0

[flying008 · Accepted solution]

Hi, @Danah_mr 

1- If use Macro of Excel-Transpose function in gallery,  the case will so easy.

2- Sum the total then calculation %, will get your want.

[Danah_mr]

could you please share the Workflow you used on 2- Sum the total then calculation % ? i need to see the configration and sitting of the Append tool

Thank you @flying008 

[flying008]

Hi, @Danah_mr 

The append tool don't need any setting for additional,  if you have other questions, let me know.