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 Element2020202120223 Yr AverageTotal DCC$164,406,097$178,020,724$187,171,952$176,532,924Total Material Cost$14,757,805$18,857,448$22,690,419$18,768,557 Desired Output: Cost Element2020202120223 Yr AverageTotal DCC$164,406,097$178,020,724$187,171,952$176,532,924Total Material Cost$14,757,805$18,857,448$22,690,419$18,768,557Material as % of DCC9%11%12%11% Thanks!

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.

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!

Workflow

Developer

Output

Accepted answers

Qiu

@Danah_mr
A quick sample can be something like this.

A lot of Tanspose and Cross Tab 😁

0305-Danah_mr.yxmd

flying008

Hi, @Danah_mr

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

录制_2024_03_05_14_55_55_996.gif

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

录制_2024_03_05_14_56_36_83.gif

All comments

Qiu

@Danah_mr
A quick sample can be something like this.

A lot of Tanspose and Cross Tab 😁

0305-Danah_mr.yxmd

Danah_mr

thank you !

the Tanspose and Cross Tab is a magic ✔️

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