Forum Discussion
Solved
Calculated table based on 2 different tables.
Hello everyone,
Newbie here! Any help would be very much appreciated!
Table 1 (unique ID)
Net amount based on uniques ID
| ID | net |
| 1 | 200 |
| 10 | -300 |
| 8 | -60 |
| 9 | 100 |
| 12 | 150 |
| 25 | 60 |
| 23 | -60 |
| 11 | 50 |
| 13 | -90 |
| 260 | -500 |
| 630 | 500 |
| 30 | 622 |
| 38 | -900 |
| 50 | -86 |
| 95 | 50 |
| 5 | 33 |
Table 2
each Employee or Team could have multiple IDs.
| ID | Employee or team |
| 1 | Team stars |
| 10 | Team stars |
| 8 | Adam |
| 9 | josh |
| 12 | Team PBI |
| 25 | clara |
| 23 | james |
| 11 | Team python |
| 13 | Team stars |
| 260 | abe |
| 630 | jessica |
| 30 | Team winners |
| 38 | Cain |
| 50 | Florence |
| 95 | Oliver |
| 5 | Elias |
| 1 | Team stars |
| 10 | Team stars |
| 55 | Nancy |
| 500 | Team Venus |
| 39 | taylor |
| 25 | Frank |
| 69 | abe |
| 11 | team python |
| 76 | clark |
| 66 | stephanie |
| 625 | steven |
| 30 | Team winners |
| 44 | sarah |
| 98 | mary |
| 22 | jacob |
| 6 | sven |
Table 3 ( the result)
The result I'm seeking is an aggregated net amounts by unique (employee or team), as a Table in Data tab not in Report tab in Power bi.
FYI, I have managed to get the below result in the report tab but couldn't replicate the table in the Data tab.
| Employee or Team | net |
| Team stars | -190 |
| Adam | -60 |
| josh | 100 |
| Team PBI | 150 |
| clara | 60 |
| james | -60 |
| Team python | 50 |
| abe | -500 |
| jessica | 500 |
| Team winners | 622 |
| Cain | -900 |
| Florence | -86 |
| Oliver | 50 |
| Elias | 33 |
Relationship between Table 1 and Table 2 ( one to many, 1---->----* )oneway.
Thanks in advance
There are ties in ranking,
6 Replies
= CALCULATE( SUM( Table1[net] ), CALCULATETABLE( Table2 ) )Table3 = SUMMARIZECOLUMNS( Table2[Employee or team], Table2, "Sum", SUM( Table1[net] ) )
