Get sum of amount for each account based on aggregation of contracts

Question

I imported the following table which is a join from SQL. The important columns are account_id, contract_id and amount.

I want to get the amount per account (harder than it sounds) the raw import looks something like the table in the description. Neither the contract_id nor the account_id are unique, but I need to group and average the contracts and then sum those averages and group them by account. thanks in advance, really I appreciate the help.

As you can see in the example, contract "a" was repeated therefore we only take it into consideration once

Answer 1

You can also do this in Power Query (Transform):

• Remove Duplicates
• Group by Account
• Select to Aggregate by SUM for the Amount column

M Code
generated by the UI

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYkMDpVgdDK4RkJkE4prCuclAbASRNQYyU0GyZkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Contract = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Account", Int64.Type}, {"Contract", type text}, {"Amount", Int64.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
   
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Account"}, {
        {"Amount Sold", each List.Sum([Amount]), type nullable number}
        })
in
    #"Grouped Rows"

Answer 2

If its a second table you want then you can do it like this:

Result = 
SUMMARIZE(
    'Data',
    [Account],
    "Amount Sold", 
    SUMX(
        SUMMARIZE(
            'Data',
            [Account],
            [Contract]
        ),
        AVERAGE(Data[Amount])
    )
)

Where 'Data' is your input data table.

Update: Image added with your cod in your comment:

Formatted code: