Forum Discussion
Helper IHow 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 % - % 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?
5 Replies
- amitchandak
Super User
questions ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
questionsHere 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-msftCommunity Support
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
