Sum all Values in one column depending on Values of other column

[pedrams]

Hi all,

I would like to sum the values in the column "amount" for all unique values in column "merchant-order-id" and only keep the uniqe value of "merchant-order-id" with the summed "amount".

I hope my question is clear. Thank you!

[MarcoRotta]

Hi pedrams.

SUMMARIZE function can do that to you. Docs and samples here: https://msdn.microsoft.com/en-us/query-bi/dax/summarize-function-dax 

At a glance, just go to "Modeling" and select "New Table". For your sample data it would be something like:

NewTable = SUMMARIZE(YourTable, YourTable[merchant-order-id], "Amount", sum(YourTable[Amount]))

In the formula above, "YourTable" should be changed to your current data table, which you did not mention. "New Table" could be renamed to anything you like. 

Regards. 

[pedrams]

Hi Marco,

thank you for the solution. But I lose all the other columns, how can I keep all the other columns?

Best,

Pedram

[MarcoRotta · Accepted solution]

Yes. Just add all the columns you want to keep here:

NewTable = SUMMARIZE(YourTable, YourTable[merchant-order-id], COLUMN1, COLUMN2, "Amount", sum(YourTable[Amount]))

PS.: if you think my answer solves your problem, don't forget to mark the post as solution. 🙂

Regards.