Sum by customer but without grouping / total sales per customer

[R3mi]

Hey !

I need a little help for something I thought it would be simple but I'm getting stuck  😅 .

I have a table where each row represents an order line with a product code, the price and quantity per customer it look like this : 

CustomNumber	OrderNumber	ProductNumber	Qty	Price
001	001001	11	1	100
001	001001	12	2	200
002	002001	11	1	100
002	002001	12	1	200
002	002002	13	1	300
002	002002	14	2	400

Now I need a new column with the total sale per customer, but I don't want to use the group by customer method because I need to keep each row for another step.
I want something like this :

CustomNumber	OrderNumber	ProductNumber	Qty	Price	Total per customer
001	001001	11	1	100	500
001	001001	12	2	200	500
002	002001	11	1	100	1400
002	002001	12	1	200	1400
002	002002	13	1	300	1400
002	002002	14	2	400	1400

So first I have created a new column with the Total per product (Price x Qty) but now i'm struggle and can't find a formula without group per customer...
Maybe I need to create another request ? an then add both ?
Thanks  !

[AlienSx · Accepted solution]

Hi, @R3mi add Price x Qty, group by customer (sum new column and retain all columns), then expand missing columns. Table.Group is your friend. 

let
    Source = your_table,
    ttl_prod = Table.AddColumn(Source, "total_product", each [Qty] * [Price]),
    group = Table.Group(
        ttl_prod, "CustomNumber", 
        {{"all", each _}, 
        {"Total per customer", each List.Sum([total_product])}}
    ),
    expand = Table.ExpandTableColumn(group, "all", {"OrderNumber", "ProductNumber", "Qty", "Price"})
in
    expand

[R3mi]

Thanks !

I just missed the last step "expand" ... so simple 😅