Measure for Sum total using Line items

[Anonymous]

Hi I need help. Please!

I am trying to create a measure to give me the sum amount for each ID Number. Some items have many Line Item, while others have only 1.  Unfortunately the Amount field can be repeated due to the data structure. It should not be double counted since it is the same Line Item. This is where I am having issues with distinct functions and calculating the sum. Example is below.

Unfortunately the data is set up through a live connection so no calculated columns or tables can be made.

Any suggestions to calculate the total amount for each ID number? It would be great if the end result only appears once when placing this in a table, but I know thats a bit much.

Thanks!

ID Number	Line Item	Amount	Trans#
125	1	60	1
125	2	50	1
125	3	40	1
125	4	30	1
125	5	10	1
125	1	60	2
125	7	50	1
125	8	10	1
526	1	20	1
526	2	25	1
17	1	10	1

[dax]

Hi @Anonymous , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

[amitchandak · Accepted solution]

@Anonymous , Try like

sumx(summarize(Table, Table[ID], Table[Number Line], Table[Item Amount]),[Item Amount])

[mahoneypat]

Please try this expression to sum thd distinct list of numbers.  You can use it in a table visual with the ID numbers.

Sum Distinct = SUMX(VALUES('ID'[Amount]), 'ID'[Amount])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat