Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Complicated Aggregation of multiple Tables using Measures and DAX RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    Saturday, April 11, 2020 4:23 AM

Answers