Forum Discussion

c_slater_1's avatar
c_slater_1
New Member
4 years ago
Solved

Calculate total from different tables

Hi,   I am looking for some help in trying to take data from 2 tables and add them together to get a total.   I have taken the data in table 1 below and created a matrix to get a total per day bu...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I did not create any relationship between two tables, and the below is for creating a new table.

     

     

    New table = 
VAR _newtable =
    ADDCOLUMNS (
        SUMMARIZE ( Table1, Table1[Date], Table1[Person] ),
        "@Time from task 1", CALCULATE ( SUM ( Table1[Time from task 1] ) ),
        "@Time from task 2",
            SUMX (
                FILTER (
                    Table2,
                    Table2[Date] = EARLIER ( Table1[Date] )
                        && Table2[Person] = EARLIER ( Table1[Person] )
                ),
                Table2[Time from task 2]
            )
    )
VAR _addtotal =
    ADDCOLUMNS (
        _newtable,
        "@Total time for all tasks", [@Time from task 1] + [@Time from task 2]
    )
RETURN
    _addtotal
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi c_slater_1 
Here is a sample file with the proposed solution https://we.tl/t-rhEQ2NQ2FU

Time Task 1 = SUM ( Task1[Time] )
Time Task 2 = SUM ( Task2[Time] )
Total Time = [Time Task 1] + [Time Task 2]
c_slater_1's avatar
c_slater_1
New Member
3 years ago

Hi tamerj1

 

Thank you for you initial reply. I'm now starting to expand my use of power BI and looking at joining some of my tables.

 

Would it be possible for you to add the same file again so I can understand how you pulled the date and person fields into the same table to then allow you to input the calculations?

 

Thank you in advance