dax formula - column to row without merging

[Anonymous]

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

[Greg_Deckler]

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.

[Anonymous]

Hi Greg,

The data is original until valid to.
When I set it as source in excel, i create the additional column starts from valid from week, valid to week, total week involve and set formula for each week x, week y, ...
So that when new data are coming in, it can determine to which week the volume should be distributed in order to create pipeline.

I guess this is the one that create the problem.
Is this possible if we can set dax to identify the pipeline for each week?

Thanks