Wrong column total relationing two tables

[PietroP]

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:

NewColumn = calculate (sumx(Table2,Table2[TotKW]) )

But it didn't work

Can you please hel me?

Thank you

[Anonymous · Accepted solution]

@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.

[VahidDM]

Hi @PietroP 

Try this:

Measure = sum('Table 2'[TOTKW])

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[amitchandak]

@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.

[PietroP]

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