Forum Discussion

Helper I

6 years ago

How to summarize data by calculating from another table?

I have few Master Tables as below: 1. Master Raw - Show invoice details (trade lane - From & to and date) 2. Estimate amount - Show the estimated amount of each invoice 3. Split % ...

Tutorial Requests

Helper I

6 years ago

Here is the raw data in table format: amitchandak

1. Master Raw

Invoice no.	From	To	Date	Amount
A123	Hong Kong	New York	5-Jul-20	100
B123	Hong Kong	New York	10-Jul-20	200
C123	Japan	Shangai	11-Jul-20	100
D123	Japan	Shangai	12-Jul-20	200
E123	Japan	Shangai	16-Jul-20	100
A234	Hong Kong	New York	5-Jul-20	300
B234	Hong Kong	New York	10-Jul-20	100
C234	Hong Kong	New York	12-Jul-20	100
D234	Hong Kong	New York	16-Jul-20	100
E234	Hong Kong	New York	18-Jul-20	200
F123	Hong Kong	Singapore	5-Jul-20	500

2.Estimated Amount

Invoice no.	Estimated Amount
A123	100
B123	200
C123	100
D123	200
E123	100
A234	300
B234	100
C234	100
D234	100
E234	200
F123	500

3.Split %

From	To	Sales	Sales Return	Effective Start Date	Effective End Date
Hong Kong	New York	Andy	30%	5-Jul-20	11-Jul-20
Hong Kong	New York	Tom	70%	5-Jul-20	11-Jul-20
Hong Kong	New York	Andy	20%	12-Jul-20	18-Jul-20
Hong Kong	New York	Tom	80%	12-Jul-20	18-Jul-20
Japan	Shangai	Ivy	50%	5-Jul-20	11-Jul-20
Japan	Shangai	Jess	30%	5-Jul-20	11-Jul-20
Japan	Shangai	Mandy	20%	5-Jul-20	11-Jul-20
Japan	Shangai	Ivy	60%	12-Jul-20	18-Jul-20
Japan	Shangai	Mandy	40%	12-Jul-20	18-Jul-20

4. Actual Amount

Week	Sales	Actual Amount	From	To
28	Andy	50	Hong Kong	New York
28	Andy	50	Hong Kong	New York
28	Andy	100	Hong Kong	New York
28	Tom	50	Hong Kong	New York
28	Tom	50	Hong Kong	New York
28	Tom	50	Hong Kong	New York
28	Ivy	100	Hong Kong	Singapore
28	Ivy	50	Hong Kong	Singapore
28	Ivy	300	Hong Kong	Singapore
28	Jess	150	Japan	Shanghai
28	Jess	50	Japan	Shanghai
28	Mandy	200	Japan	Shanghai
28	Mandy	80	Japan	Shanghai
29	Tom	150	Hong Kong	New York
29	Tom	150	Hong Kong	New York
29	Tom	50	Hong Kong	New York
29	Ivy	80	Hong Kong	New York
29	Ivy	100	Hong Kong	New York
29	Jess	180	Japan	Shanghai
29	Mandy	200	Japan	Shanghai

5. Week

Date	Week
5-Jul-20	28
6-Jul-20	28
7-Jul-20	28
8-Jul-20	28
9-Jul-20	28
10-Jul-20	28
11-Jul-20	28
12-Jul-20	29
13-Jul-20	29
14-Jul-20	29
15-Jul-20	29
16-Jul-20	29
17-Jul-20	29
18-Jul-20	29

Helper I

6 years ago

My goal is to show both forecasted Sales & Actual sales together into 1 table + the % of difference.

How can I eventually showing two in one?

v-juanli-msft
Community Support
6 years ago
Hi questions

I have problems creating "Forecast sales" as shown.

Could you show the relationship among your tables?

Best Regards

Maggie

Related content