Average of values, but not on item level...
I am building a query for reporting production times, but I have huge problems when I try to report on averages - for example average order time per plant or material.
I have narrowed it down to the fact that even though the value I want to average is stored at the item level (operation),it is related to a higher aggregation level (order). I'm sure there must be some way to solve this, but I just can't find it, so now I'm hoping you guys can help me out!
In my cube I have line items like this (simplified - there are more aggregation levels):
Plant, Order, Operation, operation runtime, order runtime, rowcount
Plant1, Order1, operation1, 2days, 5days, 1
Plant1, Order1, operation2, 4days, 5days, 1
Plant1, Order2, operation1, 1days, 10days, 1
Plant1, Order2, operation2, 3days, 10days, 1
Plant1, Order2, operation3, 4days, 10days, 1
Plant1, Order2, operation4, 3days, 10days, 1
Plant1, Order2, operation5, 4days, 10days, 1
Plant1, Order2, operation6, 1days, 10days, 1
Note that the operation runtimes do not sum to the order runtime (because they are calendar days and some operations run in parallel).
In the report, I want to show average "order runtime" at the different aggregation levels - and allow drill-down/up.
If I make a simple formula that divides "order runtime" by "rowcount", I get the right value per order, but at higher aggregation levels, the average is weighted with the number of operations per order, so the example above would give an average for plant1 of 8,75 insteaf of 7,5.
I've tried using exception aggregation, but I just can't seem to get it right...