Combine data in two tables to measure cost per operation

DAX DAX Calculations
1

Hi,

I have been tasked to set up a new costing model for a department at my company.

The setup in the department is as follows:

All operations are based on work orders. Every time there is a new piece of equipment coming into their production line there is a new work order (WO) created in the ERP. At the same time there is a line created, with the same WO number, in a WO tracker outside the ERP

My available sources for data is

  • ERP which contains info on account, period, amount, quantity, WO,
  • WO tracker which contains info on WO, serial number, part number, customer, project etc.

And lastly, a table from our ERP where there is info on the status of the WO (Started, in progress, finished)

I have uploaded an excel which show what type of data i have available,

What I want to end up with is a model where I can measure how much money is spent every time a piece of equipment has gone through the production line. I.e. by part number, serial number, check for variances between customers etc…

My current challenge is is how do i get started, and also I’m a bit worried about data quality.
Mainly because there seems to be quite a few cases where there are multiple unique Serial/part numbers within one WO, meaning that there i no way to know what cost belongs to which serial/part number within the WO.

Any tips on how to get started would be highly appreciated!

Brgds
Anders

scenario.xlsx (12.1 KB)

2

Ok nice scenario.

I’ve started on a bit of a mock up to give you some ideas.

Just before I dive in. Certainly check out this module when you have time. This is the key to understanding who to create optimal models in Power BI.

My thinking for models always starts with how simple can I make it. I also look to work out which is a fact table and which is a lookup table.

This was my initial thoughts.

image
image758×437 8.56 KB

I would also definitely add a date table in here.

Anything date related should have a date table included in the model.

http://portal.enterprisedna.co/courses/powerbisuperuserworkshop/lectures/1772347

You might need to get smart about the relationships here as you only have year and period in your fact table (cost info) but it’s all possible.

I’ve started with some simple formulas.

image

Here’s an example of how you can work out customer information from the costs table.

Because they are in two different tables you need to do it with formula.

See below.

Total PCLC by Customer = 
CALCULATE( [Total PCLC],
    INTERSECT( VALUES( 'Cost Info'[Order] ), VALUES( 'Order Tracker'[Order] ) ) )

image
image782×347 13.1 KB

I’ve attached the mock up. See how you go with this.

Attached
costing model.pbix (65.5 KB)