# Complicated Aggregation of multiple Tables using Measures and DAX

• ### Question

•  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

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

Monday, April 13, 2020 7:48 AM