Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 !
Solved! Go to 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
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
Thanks !
I just missed the last step "expand" ... so simple 😅