Forum Discussion
Wrong column total relationing two tables
Hi everyone,
i have two tables:
table1:
SiteCode,
Work_ID,
Date_Of_Work
ex:
| work_ID | SiteCode | date_of_work |
| 2021CF000030 | D03125 | 11/03/2021 00:00 |
| 2019CF000017 | D00019 | 19/09/2019 00:00 |
| 2019CF000089 | D02370 | 04/12/2019 00:00 |
| 2020CF000023 | D02454 | 29/01/2020 00:00 |
| 2019CF000074 | D02336 | 15/11/2019 00:00 |
| 2019CF000098 | D02398 | 16/12/2019 00:00 |
| 2020CF000034 | D02615 | 21/02/2020 00:00 |
| 2020CF000104 | D02835 | 04/08/2020 00:00 |
| 2020CF000019 | D01679 | 31/01/2020 00:00 |
| 2020CF000018 | D02445 | 28/01/2020 00:00 |
| 2020CF000020 | D01543 | 31/01/2020 00:00 |
table2:
SiteCode,
TOTKW
Ex:
| SiteCode | TOTKW |
| D03125 | 950 |
| D00019 | 948 |
| D02370 | 576 |
| D02454 | 491 |
| D02336 | 300 |
| D02398 | 250 |
| D02615 | 249 |
| D02835 | 222 |
| D01679 | 215 |
| D02445 | 200 |
| D01543 | 150 |
I made a relationship many-to-one between the two tables (table1 to table2) on SiteCode and I need to get a table with SiteCode, Work_ID, Date_Of_Work, and TOTKW
The problem is that TOTKW field total is not the sum of the row values displayed.
I tried to solve creating a new column in table two with this formula:
Can you please hel me?
Thank you
PietroP
You may just create a column in Table 1 to get the TOTKW from table2, this should be more clear to you.TOTKW = Related(table2[TOTKW])
Then you can just create measure or column with simply sum.
measure = sum(table1[TOTKW])Best regards
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
PietroP , Try like
NewColumn = calculate (sumx(values(Table2[Sitecode]),calculate(Max(Table2[TotKW]) )))
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Thank you for your suggestion. It didnt work, I still have the same total.
I updated my first post pasting real datas about the two tables. Hope it could help.
Tell me if you need anything different.
Thanx
PietroP
You may just create a column in Table 1 to get the TOTKW from table2, this should be more clear to you.TOTKW = Related(table2[TOTKW])
Then you can just create measure or column with simply sum.
measure = sum(table1[TOTKW])Best regards
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
