Calculating Margin when intercompany transactions skew data - Mapping Logic

[PeterNicholson]

Hello everyone.  I am currently building a sales revenue dashboard for my company.  We are now implementing profit margin into the dashboard, but we have a lot of intercompany transactions, which complicates margin reporting.

For example, we may make a product at one location and sell it to another part of our company who then sell it to the customer.  We need to essentially factor out the intercompany costs. I have an example:

The UK sell a “11195-94” to a customer for $100.  This part is made at our Sweden location where it costs $15 to make, and they sell it to the UK for $30.

When the UK office and Sweden office submit data, we see this in our database for example:

The Sweden to UK transaction is intercompany so gets filtered out of our dashboard, but we need to have the margin calculate on the UK revenue as:

Margin = UK revenue – Sweden Cost

Correct Margin = $100 - $15 = $85

Wrong Margin = $100 - $30 = $70

Note that sometimes the product is called different things in different locations but we have a “product mapping” table to link them together.

How can I start trying to figure this out so margin is reporting correctly?

There’s MANY examples of where this happens in our business and the below gives a breakdown of all the potential intercompany routes a product can take.  I’ve included an example for each and shown what the Margin Calculation should be.

I can provide a schema for our data warehouse if this is also needed.  And I appreciate this is quite a complex problem I have!  Note that I have already anonymised the data in order to post this.

Thanks,

Pete

[PeterNicholson]

Hi @v-shex-msft - thanks for your patience on this one.  It took me a while to put together some dummy data and a .pbix file.  Please see the .pbix file here. Link to .pbix file   

The screenshot below shows an example:

This is based on the mapping I have been given:

Thanks,

Pete

[v-shex-msft]

HI @PeterNicholson,

It seems like a common issue that appear when you try to use measure expire not calculate with multiple level aggregations, you can refer to the following blog know how to handle this scenario:

Measure Totals, The Final Word 
Regards,
Xiaoxin Sheng

[PeterNicholson]

Hi @v-shex-msft Thanks for replying.  I'll be putting a small data extract and .pbix file together and will reply to this thread when I've done it in the next couple of days. Appreciate your help so far.

[v-shex-msft]

Hi @PeterNicholson ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng