Hi,
I have 2 excel tables as the source in Power BI
1 table - called pipeline - the volume that is set with valid to and from - will be divided in corresponding weeks.
Sales | Valid From | Valid To | Volume | Valid From Week | Valid To Week | Total Week Involve | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Week 8 | Week 9 | Week 10 | Week 11 | Week 12 | Week 13 | Week 14 | Week 15 | Week 16 | Week 17 | Week 18 |
A | 1-Jan-20 | 31-Mar-20 | 50 | 1 | 14 | 14 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 3.571429 | 0 | 0 | 0 | 0 |
B | 14-Feb-20 | 30-Apr-20 | 200 | 7 | 18 | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 16.66667 | 16.66667 | 16.66667 | 16.66667 | 16.66667 | 16.66667 | 16.66667 | 16.66667 | 16.66667 | 16.66667 | 16.66667 | 16.66667 |
C | 1-Mar-20 | 31-Mar-20 | 300 | 9 | 14 | 6 | 50 | 50 | 50 | 50 | 50 | 50 | ||||||||||||
D | 1-Feb-20 | 31-Mar-20 | 60 | 5 | 14 | 10 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 6 |
1 table - called target - the weekly target volume each week
Target | Volume | Sales |
Week 1 | 50 | A |
Week 2 | 50 | A |
Week 3 | 50 | A |
Week 4 | 50 | A |
Week 5 | 50 | A |
Week 6 | 50 | A |
Week 7 | 50 | A |
Week 8 | 50 | A |
Week 9 | 50 | A |
Week 10 | 50 | A |
Week 11 | 50 | A |
Week 12 | 50 | A |
Week 13 | 50 | A |
Week 14 | 50 | A |
Week 15 | 50 | A |
Week 16 | 50 | A |
Week 17 | 50 | A |
Week 18 | 50 | A |
Week 19 | 50 | A |
Week 1 | 60 | B |
Week 2 | 60 | B |
Week 3 | 60 | B |
Week 4 | 60 | B |
Week 5 | 60 | B |
Week 6 | 60 | B |
Week 7 | 60 | B |
Week 8 | 60 | B |
Week 9 | 60 | B |
Week 10 | 60 | B |
Week 11 | 60 | B |
Week 12 | 60 | B |
Week 13 | 60 | B |
Week 14 | 60 | B |
Week 15 | 60 | B |
Week 16 | 60 | B |
Week 17 | 60 | B |
Week 18 | 60 | B |
Week 19 | 60 | B |
Week 1 | 25 | C |
Week 2 | 25 | C |
Week 3 | 25 | C |
Week 4 | 25 | C |
Week 5 | 25 | C |
Week 6 | 25 | C |
Week 7 | 25 | C |
Week 8 | 25 | C |
Week 9 | 25 | C |
Week 10 | 25 | C |
Week 11 | 25 | C |
Week 12 | 25 | C |
Week 13 | 25 | C |
Week 14 | 25 | C |
Week 15 | 25 | C |
Week 16 | 25 | C |
Week 17 | 25 | C |
Week 18 | 25 | C |
Week 19 | 25 | C |
Week 1 | 60 | D |
Week 2 | 60 | D |
Week 3 | 60 | D |
Week 4 | 60 | D |
Week 5 | 60 | D |
Week 6 | 60 | D |
Week 7 | 60 | D |
Week 8 | 60 | D |
Week 9 | 60 | D |
Week 10 | 60 | D |
Week 11 | 60 | D |
Week 12 | 60 | D |
Week 13 | 60 | D |
Week 14 | 60 | D |
Week 15 | 60 | D |
Week 16 | 60 | D |
Week 17 | 60 | D |
Week 18 | 60 | D |
Week 19 | 60 | D |
in PowerBI, I would like to have this kind of result. Target from the sum of value each week from table target, and pipeline the sum based on week from table pipeline
Target | Target | Pipeline |
Week 1 | 195 | 3.571429 |
Week 2 | 195 | 3.571429 |
Week 3 | 195 | 3.571429 |
Week 4 | 195 | 3.571429 |
Week 5 | 195 | 3.571429 |
Week 6 | 195 | 3.571429 |
Week 7 | 195 | 26.2381 |
Week 8 | 195 | 26.2381 |
Week 9 | 195 | 76.2381 |
Week 10 | 195 | 76.2381 |
Week 11 | 195 | 76.2381 |
Week 12 | 195 | 76.2381 |
Week 13 | 195 | 76.2381 |
Week 14 | 195 | 76.2381 |
Week 15 | 195 | 16.66667 |
Week 16 | 195 | 16.66667 |
Week 17 | 195 | 16.66667 |
Week 18 | 195 | 16.66667 |
Currently, to achieve the table in PowerBI - I am using merge queries & aggreagate by creating unique identifier to link both table, at first it works perfectly. Now with the data are coming in, the loading process is taking a bit too long.
So I am to streamline the process through Dax formula where I can use to create this table without doing the function of merge queries & aggregate.
Need the help here!
Thanks in advance.
Cheers,
Mei
I would strongly considering unpivoting your Week columns in your pipeline table. You could then use SUMMARIZE on your target table by Week, SUM your Volume column and then probably do a LOOKUPVALUE to your pipeline column or a SUMX with a FILTER on RELATEDTABLE.