Complicated Aggregation of multiple Tables using Measures and DAX

[madhurmalhotra]

3 INPUT Tables Owned by Table3- 70%
Table1 - Standalone	Amt
SC	150000
Table1 RE	100000
L	200000
Total L	450000
I	180000
A	270000
Total A	450000

Owned by Table1- 60%
Table2 - Standalone	Amt
SC	300000
Table2 RE	40000
L	100000
Total L	440000
I	0
A	440000
Total A	440000

Ultimate Parent
Table3- Standalone	Amt
SC	100000
RE	50000
L	150000
Total L	300000
Investment	105000
A	195000
Total A	300000

2 OUTPUT FILES

Output 1 =Table1-consolidated Table 1(Input 1) + Table 2 (Input 2) + ROWS PASSED

Table1 - consolidated	Amt
SC	270000
Table1 consol RE	124000
L	300000
Table1 reserve	16000
Total L	710000
I	0
A	710000
Total A	710000

Table3 - consolidated     AMount 

SC	265000
RE	136800
L	450000
Table3 reserve	53200
Total L	905000
I	0
A	905000
Total A	905000

Output 2 =Table3-consolidated

Input 3(Table3) + Output 1+ROWS passed

1.  

Q2. POWER BI should be able to identify that Ouptut 1 is consolidation of Table2 and Table1 and Output 2 is consolidation of Table3 and Output 1 (Table1+table2 consolidated)

Q3. POWER BI should be able to insert a new row in Output 1 with the name 'Table1 reserve' by multiplying the 'Table2 RE' by 40%

Q4. Ouput 2 should be created aggregating Ouput 1(also carry forward the new inserted rows as mentioned in above lines) with Input 3

Q5. POWER BI should be able to insert a new row in Output 2 with the name 'Table3 reserve' by multiplying the 'Table1 consol RE' by 30%

Q6. POWER BI should be able to insert a new row in Output 2 with the name 'Table3 reserve' by multiplying the 'Table1 consol RE' by 30%

Q7.At last we should get only single table(Consolidating All) and filters for diiferent levels (Output -1,Output-2, Standalone data of Table1,Table2,Table3) 

Q8. Can there be any reference created to automatically aggregate (Table1 and Table2- or any other similar combination if the number of tables are high) tat is OUTPUT1,the identifying this aggregation is be carry forward and aggregated with Table3

Q9. Please use measures rather than making columns/tables and there are more than 100 tables like this so making this logic for these three can be replicated on other tables as well

[MFelix]

Hi @madhurmalhotra ,

Your post is very confusing about the data itself and expected output.

Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

If you are abble to provide a sample data and more explicit calculation and expected results would be better.

Also the question you place are very confusing, but there is one thing that called my attention on the last question your model is over 100 tables?