Combined Orders Summarized - Text Description Fields

Power Query
1

Hello,

I’m not sure if there is a solution to this, but I figured I will create a thread and see what the community has to say.

I mocked up some data which should be fairly close to my real data.

Combined Orders Summarized.pbix (113.8 KB)
Combined Orders summarized.xlsx (180.1 KB)

Basically I have two fact tables.

Price = Individual Orders at the item level by Company
Shipping Cost = Individual and Groups of Sales Orders at the charge type level

I created an Order Groups Table using the Shipping Cost.

This is based off a text field (and my really data may even have 3 or 4 orders in this field), so joining the tables, only allows me to link on the individual orders, thus loosing view of the grouped orders.

Does anyone know of a way to summarize the combined orders, so I can properly join the data?

For instance row 2 in the Shipping Costs tab has ORD00001 and ORD00009. The cost is $44.55.
I want to be able to link it to the Price tab by looking up both orders and summarizing them.

ORD00001 = $6.80 + $5.20 + $27.00 = $39.00
ORD00009 = $18.90

ORD00001 + ORD00009 = $39.00 + $18.90 = $57.90

So that I can then run the calculation to compare the cost to the summarized value (price)

$57.90 - $44.55 = $13.35 = Value Above/Below Cost
$13.35 ÷ $57.90 = 23.06% = Value vs. Cost Pct

Any ideas or solutions are appreciated.

2

Hi @ibesmond ,

There are multiple ways to attack a problem like this… maybe this works for you.
Just change the FileLocation parameter to restore the Queries.

image
image1467×364 36.3 KB

Here’s your sample.
Combined Orders Summarized.pbix (120.8 KB)

2 Likes
3

:smiley: @Melissa

Thanks for the response.

Grouped Cost Missing

I’m a little confused with the formulas you created and the reasoning behind adding the underscores to the order numbers. Further, I don’t know how to get the combined values to appear in the groups. The example you sent back shows all blanks when two orders are on one shipment.

4

Hi @ibesmond ,

Well, you are looking at your own figures…
I didn’t load the data to the model, just set everything up in PQ. So you still had to do a little work…

As for the underscores, you order ids aren’t fixed length so looking for the string ORD00001 will get you everything from ORD00001 up to ORD0000199 (this might be due to the fact that this is a sample, only you know for sure).

When loaded to the model and a few simple measures, it look like this.

image

Combined Orders Summarized.pbix (157.9 KB)
I hope this is helpful.

1 Like
5

Hi @Melissa

Thank you for going the extra steps to help me understand the logic behind the solution. :smiley:
:clap: I believe that my actual data does have a standard length. You have been very helpful.

1 Like
6

@Melissa,

Those initial table buffer steps threw me for a loop. Are you aware of any content on those PQ functions within EDNA platform?

7

Hi @ibesmond ,

No structured content on this specific topic (yet)…
eDNA is working hard to provide more content, you might have noticed that they have been releasing a new course every month this year and plan to continue that throughout 2021. Among them will be a Power Query course (quite soon I believe) and future courses on PQ are also planned…
.
But using the Search option - you might find some intresting/ related posts within the forum.