I am a beginner and could use your help to build a workflow. Thank you so much in advance. I am given following three input tables: Table 1 - Amount by Customer by State by Month Table 1 - Amount Table Cust IDStateJanFebMar2GA1015252CA2025352NY3035454CA2530404GA152030 Table 2 - Weights table. This table need to multiply with Table 1 (joining by State) Table 2 - Weight Table StateJanFebMar GA 0.80 0.70 0.50 CA 0.70 0.60 0.40 NY 0.60 0.50 0.30 Table 3 - Rates table (for price and cost) Table 3 - Rates TableCust IDRevenueCost1 10.0 3.02 6.0 2.03 8.0 1.04 7.0 5.05 4.0 5.0 Here is the output table: Intermediate step - multiple table 1 and table 2 Output by multiplying Table 1 and 2 (Join by State)Cust IDStateJanFebMar2GA8.0010.5012.502CA14.0015.0014.002NY18.0017.5013.504CA17.5018.0016.004GA12.0014.0015.00 Here is final output table by applying rates: Final Output Needed LineCust IDStateJanFebMarRevenue2GA 48.00 63.00 75.00Revenue2CA 84.00 90.00 84.00Revenue2NY 108.00 105.00 81.00Revenue4CA 122.50 126.00 112.00Revenue4GA 84.00 98.00 105.00Cost2GA 16.00 21.00 25.00Cost2CA 28.00 30.00 28.00Cost2NY 36.00 35.00 27.00Cost4CA 87.50 90.00 80.00Cost4GA 60.00 70.00 75.00Profit2GA 32.00 42.00 50.00Profit2CA 56.00 60.00 56.00Profit2NY 72.00 70.00 54.00Profit4CA 35.00 36.00 32.00Profit4GA 24.00 28.00 30.00

Multiplying 3 tables to build Profit and Loss Statement

I am a beginner and could use your help to build a workflow. Thank you so much in advance.

I am given following three input tables:

Table 1 - Amount by Customer by State by Month

Table 1 - Amount Table
Cust ID	State	Jan	Feb	Mar
2	GA	10	15	25
2	CA	20	25	35
2	NY	30	35	45
4	CA	25	30	40
4	GA	15	20	30

Table 2 - Weights table. This table need to multiply with Table 1 (joining by State)

Table 2 - Weight Table
	State	Jan	Feb	Mar
	GA	0.80	0.70	0.50
	CA	0.70	0.60	0.40
	NY	0.60	0.50	0.30

Table 3 - Rates table (for price and cost)

Table 3 - Rates Table
Cust ID	Revenue	Cost
1	10.0	3.0
2	6.0	2.0
3	8.0	1.0
4	7.0	5.0
5	4.0	5.0

Here is the output table:

Intermediate step - multiple table 1 and table 2

Output by multiplying Table 1 and 2 (Join by State)
Cust ID	State	Jan	Feb	Mar
2	GA	8.00	10.50	12.50
2	CA	14.00	15.00	14.00
2	NY	18.00	17.50	13.50
4	CA	17.50	18.00	16.00
4	GA	12.00	14.00	15.00

Here is final output table by applying rates:

Final Output Needed
Line	Cust ID	State	Jan	Feb	Mar
Revenue	2	GA	48.00	63.00	75.00
Revenue	2	CA	84.00	90.00	84.00
Revenue	2	NY	108.00	105.00	81.00
Revenue	4	CA	122.50	126.00	112.00
Revenue	4	GA	84.00	98.00	105.00
Cost	2	GA	16.00	21.00	25.00
Cost	2	CA	28.00	30.00	28.00
Cost	2	NY	36.00	35.00	27.00
Cost	4	CA	87.50	90.00	80.00
Cost	4	GA	60.00	70.00	75.00
Profit	2	GA	32.00	42.00	50.00
Profit	2	CA	56.00	60.00	56.00
Profit	2	NY	72.00	70.00	54.00
Profit	4	CA	35.00	36.00	32.00
Profit	4	GA	24.00	28.00	30.00

Datasets

Accepted answers

JosephSerpis

Hi @anamik72 I mocked up a workflow and tried to design so it's dynamic as possible so can handle more months and will sort automatically in order. Let me know what you think?

Profit_Loss_10042020.yxmd

All comments

grossal

Hi @anamik72,

I think I got it:

Output:

Values seem to be right from what I checked. It's not sorted to exactly match your table yet.

What happens:

1) I Shift all the columns (besides Key-Columns) to rows for easier Join

2) I calculate the Profit right at the beginning with an additional column

3) Join everything based on key + month

4) Calculate everything (only one formula needed)

5) Shift everything back

6) Order the columns

7) Sort by Line

Workflow attached. Let me know what you think.

Best

Alex

2020-04-10-Multiplying 3 tables to build Profit and Loss Statement.yxmd

JosephSerpis

Hi @anamik72 I mocked up a workflow and tried to design so it's dynamic as possible so can handle more months and will sort automatically in order. Let me know what you think?

Profit_Loss_10042020.yxmd

anamik72

Thank you so much. This took care of it. And I found it very easy to follow. I can now scale it for entire dataset. Much appreciated.

anamik72

Thank you Alex. Yes, I follow your logic and it takes care of my issue. Thanks again for helping me out. And for being so prompt.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703