Forum Discussion
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 but I trying to add that total to the value in table 2 below to get an overall total.
I don't know how to calculate this, see some sample data below of what I am trying to achieve. My thought is I may need to create a new table from the matrix to allow me to calculate the overall total. Am I on the right lines or is there a better way to do it?
Table 1
ID | Date | Person | Time from task 1 |
1 | 01/01/2022 | Person 1 | 5 |
2 | 01/01/2022 | Person 1 | 7 |
3 | 02/01/2022 | Person 1 | 2 |
4 | 02/01/2022 | Person 1 | 4 |
5 | 01/01/2022 | Person 2 | 6 |
6 | 01/01/2022 | Person 2 | 1 |
7 | 02/01/2022 | Person 2 | 8 |
8 | 02/01/2022 | Person 2 | 9 |
Table 2
ID | Date | Person | Time from task 2 |
1 | 01/01/2022 | Person 1 | 5 |
2 | 02/01/2022 | Person 1 | 2 |
3 | 01/01/2022 | Person 2 | 6 |
4 | 02/01/2022 | Person 2 | 8 |
Table 3. New table from table 1 and 2
Date | Person | Total time from task 1 | Time from task 1 | Total time for all tasks |
01/01/2022 | Person 1 | 12 | 5 | 17 |
02/01/2022 | Person 1 | 6 | 2 | 8 |
01/01/2022 | Person 2 | 7 | 6 | 13 |
02/01/2022 | Person 2 | 17 | 8 | 25 |
Any help will be appreciated
Thanks
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
5 Replies
- Jihwan_Kim
Super User
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 _addtotalThank you for this, I have implemented against my master data sets and has worked exactly as I wanted.
Really appreciate the help
- tamerj1
Community Champion
Hi c_slater_1
Here is a sample file with the proposed solution https://we.tl/t-rhEQ2NQ2FUTime Task 1 = SUM ( Task1[Time] )Time Task 2 = SUM ( Task2[Time] )Total Time = [Time Task 1] + [Time Task 2]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
- tamerj1
c_slater_1
Please refer to attached
