Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
@questions ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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 |
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?
Hi @questions
I have problems creating "Forecast sales" as shown.
Could you show the relationship among your tables?
Best Regards
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |