Calculate SUM of main record and details in single field

[AlexeyRusinov]

Hello everyone,

I am using Query Editor to create my data model. I have two entities merged in single query: sales order and sales order detail. So detail is a primary entity, and then sales order id is linked end expanded.

Sales Order Amount	Additional Detail Amount	Sales Order Id
100	50	1
100	20	1

I need to a calculate a SUM, so that if I make an aggregation in Power BI, I see sum Sales Order plus sum of it's details.

If I make summary of all rows from example, it will show me 270 instead of correct 170.

Is it possible to achieve this in Query Editor?

[Anonymous]

Hi @AlexeyRusinov

If I understand your problem correctly, this should give you what you are looking for.

The SUMX-function evaluates a sum for each row in a given table. I then use the summarize-function to declare a table within the calculation. The new table is only returning unique rows for each order id, and thus only returns 100 once. I then just add the sum of the additional amount afterwards.

Here is the function in text, so you can copy+paste it:

Merged amount based on Order Id = SUMX(SUMMARIZE(Table1; Table1[Sales Order Id]; Table1[Sales Order Amount]); Table1[Sales Order Amount]) + SUM(Table1[Additional Detail Amount])

I hope it makes sense. I'll gladly elaborate if needed.

Best,

Martin

[Anonymous]

Hi @AlexeyRusinov

I just want to follow up on this post. Are you still committed to solve this in query editor, or did you try out my solution?

Best,

Martin

[AlexeyRusinov]

Hi @Anonymous,

Currently I am trying to implement @TomMartens's solution, will reply as results appear.

[Anonymous]

Hi @AlexeyRusinov

Just following up on whether your problem is solved or not. How did it go with Tom Martens' suggestion?

[v-huizhn-msft]

Hi @AlexeyRusinov,

If you get expected result, please mark the right reply as answer, so more people will benefit from here.

Best Regards,
Angelia

[TomMartens · Accepted solution]

Hey,

I would simply do this in two steps in the query editor:

1. group the details first and then join, in your example this would results in 1 record 100 | 70 for order id
2. create a new column adding the order amount and details amount

Hope this helps