Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |