How to add extra formulated rows to my column?

[bryanmac_92]

Hi, 

I am looking to see how can I add formulated rows to my metric column? This data below is a snapshot of the data so there are more rows.

Channel	Channel Group	Category	Month Type	Spend Type	Metric	Value	Fiscal Year
Branded Search	Branded Search	Paid Media	Current	Direct	Inquiry	168	FY22-23
Branded Search	Branded Search	Paid Media	Current	Direct	FT	3	FY22-23
Branded Search	Branded Search	Paid Media	Current	Direct	Budget-INQ	435	FY21-22
Branded Search	Branded Search	Paid Media	Actual	Direct	Budget-Spend	34650	FY21-22
Branded Search	Branded Search	Paid Media	Actual	Direct	Budget-FT	33.95081656	FY21-22
Branded Search	Branded Search	Paid Media	Actual	Direct	Spend	34119	FY21-22

This is how I would like my data to look like. The "*=" in my metrics columns are the calculations that I would like to have done.

Channel	Channel Group	Category	Month Type	Spend Type	Metric	Value	Fiscal Year
Branded Search	Branded Search	Paid Media	Current	Direct	Inquiry	168	FY22-23
Branded Search	Branded Search	Paid Media	Current	Direct	FT	3	FY22-23
Branded Search	Branded Search	Paid Media	Current	Direct	Budget-INQ	435	FY21-22
Branded Search	Branded Search	Paid Media	Actual	Direct	Budget-Spend	34650	FY21-22
Branded Search	Branded Search	Paid Media	Actual	Direct	Budget-FT	33.95081656	FY21-22
Branded Search	Branded Search	Paid Media	Actual	Direct	Spend	34119	FY21-22
Branded Search	Branded Search	Paid Media	Current	Direct	CEI	*=Spend value/Inquiry value	FY22-23
Branded Search	Branded Search	Paid Media	Current	Direct	Budget-CEI	*=Budget-Spend value/Budget-Inquiry value	FY22-23
Branded Search	Branded Search	Paid Media	Current	Direct	Inquiry to Start	*=FT value/Inquiry value	FY22-23
Branded Search	Branded Search	Paid Media	Current	Direct	Budget-Inquiry to Start	*=Budget-FT value/Budget-Inquiry value	FY22-23

Is there a way to do this?

[Yoshiro_Fujimori]

Hi @bryanmac_92 ,

Here is one way of doing this. I hope this helps.

Workflow

Formula tool expressions

CEI = [Spend] / [Inquiry]

Budget-CEI = [Budget_Spend] / [Budget_INQ]

Inquiry to Start = [FT] / [Inquiry]

Budget-Inquiry to Start = [Budget_FT] / [Budget_INQ]

Output

[bryanmac_92]

Hi @Yoshiro_Fujimori 

Thanks for this! When I tried to run yourworkflow using my input data that had more rows (x>9000), I get only about 10 rows. Can you check to see why?

Here is my data attached.

[Yoshiro_Fujimori]

@bryanmac_92 ,

Your new data is different from the original one.

In the first place, you need to define below conditions.

1. If you want to deal with multiple "Channel", you need to define the grouping rule for each Channel.

It seems to be every 8 rows, but you should define it.

2. Your new data has different "Metric"s, so the original calculation for additional rows is not applicable.