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.
<tbody>
</tbody>
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
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.
OrderNumber | OrderLineNumber | Attribute1 | Attribute2 | On-time |
Order1 | 1 | 1 | ||
Order1 | 2 | 1 | ||
Order1 | 3 | 0 | ||
Order2 | 1 | 1 | ||
Order2 | 2 | 1 | ||
Order3 | 1 | 1 | ||
Order3 | 2 | 1 | ||
Order3 | 3 | 1 | ||
Order3 | 4 | 0 | ||
Order4 | 1 | 1 |
<tbody>
</tbody>
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