How to avoid SUM from one to many relationship tables

[jareyesg]

Hi,

I have a problem, I hope somebody can help me.

I have two tables:

Projects

ID	Name
1	Project One
2	Project Two
3	Project Three

Invoice

ID	ProjectID	Invoice Number	Amount
1	1	1	$100
2	2	2	$200
3	3	2	$200

As you can see the relationship is 1:*, and there are invoices that correspond to many projects, in the example Invoice number 2. Obviosly The SUM in a Table in the report will be $500 because it is adding all the entries from the Invoice table. 

Is there any way to avoid it like grouping by Invoice number, because the correct SUM is only $300 because there is inly two invoices, but one of them is point to several projects.

Thanks in advance.

Thanks.

[amitchandak]

@jareyesg , Create a measure like

sumx(summarize(Projects, Projects[ID],Invoice[Invoice Number], "_1", max(Invoice[Amount])),[_1])

I assume Projects and Invoice are joined

[jareyesg · Accepted solution]

Thankl you for your help! It doesn't work, but finally I solve it as follows:

AmountSum =

SUMX(

    KEEPFILTERS(VALUES(Invoice[Invoice Number])),

    CALCULATE(MAX(Invoice[Amount]))

)

[Anonymous]

Hi @jareyesg 

You can create a reference table and group the table based on invoice to show max of invoce.

Power Query Editor --> Transform --> Group By

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Regards,
Pranit