Dynamically Generate a Table from Information Distributed Across Data Structure

[TonySwad]

I have multiple tables in my data structure.

I need to auto generate a table that allocates volumes from the tblCtrMtrDetail table to the individual price indices to which the volumes have price exposure. The price indices to which each tblCtrMtrDetail record’s volume is exposed are determined from the price schedule associated with the contract and identified in the tblCtrAttributes file.  Each price schedule is a weighted average of various indices.  The weight for each index used in a price schedule is in tblSchedIndexDetail for which the primary key is Sched_id & index_id.

The generated table would ideally contain the following fields:

Ctr_Id, Mtr_Id, Prod Date, Price Schedule Id, Index Id, Index Proportion, Allocated Volume (calculated field)

[Anonymous]

Hi @TonySwad,

You could create a table from Modelling tab using Distinct(tblCtrMtrDetai[Ctr_Id]), then using Lookup pull in all other columns. 

Does this help?

[TonySwad]

Thank you for your response.  That helped me with another problem.  Unfortunately,  I do not think that works for my posted problem.  Let me explain. 

For each unique Ctr_Id in tblCtrMtrDetail, I need as many rows as their are indices associated with the price schedule associated with the Ctr_Id.  For example, for Ctr_Id =1 and Mtr_Id = 1, I have volume 100.  Ctr_Id =1 has an associated Price Schedule Id =1.  Price Schedule Id = 1, has 3 price indices associated with it Index_Id ={1,2,3} with Index_Proportions of {25%,25%,50%} for index 1,2,and 3 respectively.  For the given CtrMtr combination (1,1), I would need 3 rows in my auto-generated table.

for that scenario the three output table rows would need to be:

Ctr Id   Mtr Id   Sched_Id   Index_Id   Allocated Volume

1          1           1               1              25

1          1           1               2              25

1          1           1               3              50

[v-yuezhe-msft]

@TonySwad,

Could you please share sample data of your tables?

Regards,
Lydia

[TonySwad]

The following are samples of the data in the tables relevant to my posted problem.

The following table relates to a contract entity and is used to relate a schedule to each contract.

Ctr_Id          Co_Id         Cust_Id      Sched_Id

1	1	1	1
2	2	2	2
3	3	3	3
4	1	4	4
5	2	5	1
6	3	1	2
7	1	2	3
8	2	3	4
9	3	4	1
10	1	5	2
11	2	1	3
12	3	2	4
13	1	3	1
14	2	4	2
15	3	5	3
16	1	1	4
17	2	2	1
18	3	3	2
19	1	4	3
20	2	5	4
21	3	1	1

The following table is the detail for the rate schedules.  Each rate schedule is the weighted average of one or more indices.  The table contains a row for each index used in a given rate schedule and the weighting applied to the index in that given rate schedule.

 Sched_Id  Index_Id  Index_Proportion

1	1	0.25
1	2	0.25
1	3	0.25
1	4	0.25
2	1	0.5
2	2	0.5
3	1	0.333333333
3	2	0.333333333
3	3	0.333333333
4	1	1

This table contains metered volumes assigned to a contract in a given month.

Ctr_Id      Mtr_Id    Volume          Prod Date

1	9	5,810	12/1/2017
1	19	3,081	12/1/2017
1	20	2,388	12/1/2017
2	2	2,985	12/1/2017
2	34	488	12/1/2017
2	39	8,470	12/1/2017
3	11	4,946	12/1/2017
4	25	(7,056)	12/1/2017
4	40	5,687	12/1/2017
5	4	8,325	12/1/2017
6	16	8,883	12/1/2017
6	18	(9,178)	12/1/2017
6	24	(245)	12/1/2017
6	31	(6,818)	12/1/2017
6	37	(2,811)	12/1/2017
7	17	(2,447)	12/1/2017
7	30	2,288	12/1/2017
8	27	4,516	12/1/2017
9	14	7,995	12/1/2017
9	21	4,011	12/1/2017
9	29	6,184	12/1/2017
10	26	7,255	12/1/2017
11	7	(7,500)	12/1/2017
11	33	(4,271)	12/1/2017
12	10	4,029	12/1/2017
13	5	(1,131)	12/1/2017
13	28	(877)	12/1/2017
14	1	(258)	12/1/2017
14	36	(844)	12/1/2017
14	38	(6,577)	12/1/2017
15	12	(4,918)	12/1/2017
16	3	3,982	12/1/2017
17	15	5,153	12/1/2017
17	35	(9,369)	12/1/2017
18	6	(1,346)	12/1/2017
19	8	(917)	12/1/2017
19	13	9,838	12/1/2017
20	22	(4,111)	12/1/2017
20	32	(9,260)	12/1/2017
21	23	(1,758)	12/1/2017

The problem is to allocate the metered volumes per contract-meter-month record to the individual indices to which the metered volume is exposed.  I was hopeful of creating a table with the following headings:

Ctr_Id  Mtr_Id  Prod_Mo Sched_Id  Index_Id   Volume

such that when I added the volume for a given Ctr_Id Mtr_Id Prod_Mo across Index_Id's, the total would equal the volume for the Ctr_Id Mtr_Id Prod_Mo record in the 3rd table above.

By generating such a table, I can write one set of measures on that table that allows me to report at the level of detail I require.