How to summarize data by calculating from another table?

[questions]

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 % - % of each of the sales for the trade lane under different period

4. Actual Amount - actual amount of amounts by sales by week

5. Week

I can achieve to get the estimated & actual amount by trade lane (from, to)  by week easily by a matrix table as below:

However, I want to achieve further show the volume by trade lane by week by sales (as below), can I write a measure or I would need to create a caculated column/table to capture?

[v-juanli-msft]

Hi @questions 

In your "Split%"table create a "week" num column based on the "start date" or "end date" as you liked.

Then create a column to merge "week", "from", "to", "sales" in  "Split%"table,

in "Actual Amount" table, create a column to merge "week", "from", "to", "sales",

Next create a relationship between "Split%"table and "Actual Amount" table based on the columns created above.

Fianlly you can create "Sales return" to "Actual Amount" table.

But i don't know which columns used to create "Total amount".

If you need more details, could you share the examples in excel file or just paste your data here?

Best Regards

Maggie

[amitchandak]

@questions ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[questions]

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

[questions]

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]

Hi @questions 

I have problems creating "Forecast sales" as shown.

Could you show the relationship among your tables?

Best Regards

Maggie