DAX for summing up values of one column based on another column

[Anonymous]

Hello,

I am trying to write a measure to sum up values in one column based on the selection on another column. I have two tables connected to each other on a document number. 

Table1:-

Document Number

SCR10400015

SCR10400015

and 

Table2:-

Entry No_	Document No_	Item Ledger Entry No_	Cost Amount (Non-Invtbl_)
206716	SCR10400015	192048	0
206718	SCR10400015	192049	0
255525	SCR10400015	192048	0
211079	SCR10400015	192048	0
206719	SR00000015	192049	84.93
206717	SR00000015	192048	1654.69

Now, I am trying to write a measure to calculate the sum of column 'Cost Amount (Non-Invtbl_)' using this logic-

1. When a user selects the document number from table 1, table 2 also gets filtered on the same document number(in this case SCR10400015).

2. Then select all the values corresponding to column 'Item Ledger Entry No_' against this document number(i.e 192048 and 192049)

3. Now, clear the filter on document number 'SCR10400015' but select all records in the table containing 'Item Ledger Entry No_' of 192048 and 192049; so now we will have a selection of document numbers SCR10400015 and SR00000015. 

4. Add up all the numbers on column Cost Amount (Non-Invtbl_) to arrive at a value of 1739.62.

I tried writing a DAX formula:

Mesure 1: Cost = sum(Cost Amount (Non-Invtbl_))

Measure 2: Cost Total =

CALCULATE (

[Cost],

FILTER ( 'Table2, Table2[Item Ledger Entry No_] = MAXX(

FILTER ( 'Table2', 'Table2'[Document No_] = SELECTEDVALUE( 'Table1'[Document Number] ) )

, 'Table2'[Item Ledger Entry No_]

)))

I am able to fetch only one value with this measure(84.93). Is there any way we can select all values on column 'Item Ledger Entry No_'?

Any help highly appreciated!

[wdx223_Daniel]

Cost Total = VAR _select=VALUES( 'Table1'[Item Ledger Entry No_] ) RETURN 

CALCULATE (

[Cost],

FILTER ( ALL('Table2), Table2[Item Ledger Entry No_]  IN _select))

[Anonymous]

Thank you @wdx223_Daniel . However, I am still getting an error with this formula. The document numbers on both the tables are in text format

[wdx223_Daniel]

@Anonymous  it's data type error, you need to keep both of columns to compare have same data type, integer or text.  

[camargos88]

@Anonymous ,

Have you tried remove the duplicates and clean the columns removing spaces from table 1 and relate both tables ?

Or even relating them using many to many and cleaning the values as well.

[Anonymous]

Thank you for the suggestion! I have added in an indirect relationship between the two tables. The document numbers will repeat because they have multiple items attached to them. I have uploaded a sample file for reference.

PBIX file