creating measure for On-time in-full delivery performance

[akra88]

Dear community,

im working with a dataset of order line data, that has an indicator whether or not the order line was delivered on-time (1) or delayed (0). The structure looks something like this.
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]OrderNumber[/TD]
[TD]OrderLineNumber[/TD]
[TD]Attribute1[/TD]
[TD]Attribute2[/TD]
[TD]On-time[/TD]
[/TR]
[TR]
[TD]Order1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Order1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Order1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Order2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Order2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Order3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Order3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Order3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Order3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Order4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

The goal is to get to a Performance measure that looks up for each unique order, how many positions where on it and how many were on time. If all were on time it should remember a 1 (on-time) for the entire order if even just one order line is a 0, the whole order should be 0 (delayed). In this example the measure should give me 50% as a result because orders 2 & 4 were fully on time, orders 1 & 3 had delayed order lines.

Currently I produce a normal pivot table with all individual OrderNumbers in the rows and a count and sum for the On-time indicator in the value field. I then use an if function to put the order on either 0 or 1 depending on whether the sum and the count match up. However, this is tedious to work with especially for visualisation, filtering and slicing. I was hoping that a measure in Power Pivot could do all this in the background and I can use it in a pivot depending on my attributes.

I would be grateful for any input.

I hope my problem is understandable and I'd be happy to provide more information if it isn't.

Thanks,
Andreas

 

[Ozeroth]

Hi Andreas,

Here is how I would do it (I'm calling your table Orders):

1. Create an Order Count Measure:
   

   Order Count :=
   DISTINCTCOUNT ( Orders[OrderNumber] )

2. Create a Delayed Order Count Measure:
   

   Delayed Order Count :=
   CALCULATE ( [Order Count], Orders[On-time] = 0 )

3. Create the final Performance Measure:
   

   Performance Measure :=
   1 - DIVIDE ( [Delayed Order Count], [Order Count] )

The Delayed Order Count measure works because an Order is considered delayed only if it has at least one row where On-time = 0.

You just need to include the third measure in your PivotTables.

Does this give the result you expect?

Cheers,
Owen

 

[akra88]

Dear Owen,

it worked perfectly!
Thank you very much!

All the best from Austria,
Andreas

 

[macfuller]

Andreas -

I have created something a bit more complex per our supplier contract. If the previous answer meets your needs that's great, but I can give you more info if you're interested.

We are tracking if the order is delivered within 1 business day, 2 business days, or later than that. We then produce reports from PowerPivot determining what percentage of items were delivered in each time frame.
We also track fulfillment rates. By our contract a line item is considered fulfilled if all of the quantity on the PO line ships within 1 business day. If we order 10 and only 7 show up after 1 business day then the line item is not considered fulfilled even if the remaining 3 are received on day 2.