How to get sum by group and related table?

[Christoph1972]

I have two tables:

Table Jobs:

ID; Name; TaskID
1; Job1; 1
2; Job1 ;3
3; Job1; 3
4; Job2; 2
5; Job2; 3
6; Job2; 3
7; Job3; 1
8; Job4; 2
9; Job4; 1

and a table with costs for a specific task.

Table Task:

ID; Name; €
1; Cleaning; 5
2; Testing; 10
3; Replacing; 20

I created a relation between Jobs.TaskID and Task.ID. How to get the sum (€) of tasks of a job? I like to get the following output:

Job1; 45
Job2; 50
Job3; 5
Job4; 15

I got only the following output:

That’s quite different as expected. Any ideas how to get the sum (€) of each job?

Kind regards
Christoph

[Christoph1972 · Accepted solution]

The Power Query Editor served exactly what I needed. Go to Combine and create the relation there.

Then the model shows exactly what I need:

Thanks a lot! 😊

[FreemanZ]

Hi @Christoph1972 

try to

1) remove the TaskID from the table visual. 

2) Jobs Table and Task Table shall be related one-to-many. Is that the case?

[Christoph1972]

Thanks for your reply!

Removing the TaskID doesn't fix it.

Yes, related one-to-many is correct.