Match & Sum values from another table?

[BarryFletcher]

Hi all,

I am looking for help please?

I have 2 tables - 1 table has a column called Line number.

My second table also has a column called line number but it could have 5 rows with that lne number.

How can I list the 5 items from the second table

and sum the lunghts based on the line number in the first table?

So table one would contain something like this:

Name	Line Number
Barry	12345
Tom	45678

and table 2 would have data such as below:

Line number	Length
12345	1
12345	2
45678	5
45678	3

I would like to get the sum of rows from table 2, based on the line number value in table 1?

Hope that makes sense?

[audreygerred · Accepted solution]

Hi! Your first table, the one with name and line number (line number being unique) is your dimension table. Your second table (the one with multiple rows with the same line number) is your fact table. Create a join between the two tables. In the model tab, you should see a line go from dim to fact and see a 1 by the dim table, a * by the fact table (indicating a 1 to M  join) and an arrow pointing at the fact table (which signifies that the dim table will filter the fact table. Next, create a measure for the length like this Length = SUM('YourFactTableName'[Length]). Add a table visual (or whatever viz you want) and put in the line number from your dim table and the measure you created.

[BarryFletcher]

Thank you so uch for the help and the quick reply - worked a treat.

[audreygerred]

Awesome! You are very welcome - happy to help!

[BarryFletcher]

The result should be

Line number 12345 = 3

Line number 45678 = 8