I am starting to model a datawarehouse, and would like some suggestions. We have Orders and OrderLines. I have read that it is often recommended to model this as one OrderLine fact table at the OrderLine grain, with facts relating to the Order duplicated on the multiple OrderLine rows.
Some of these Order facts are dates (e.g. OrderReceivedDate, OrderCompletedDate). We will need to report on duration between these dates. For example, the fact table will have a measure DaysReceivedToCompleted, and this will be the same value for each row with the same OrderId.
My question is about how the roll-up/aggregate will work with the redundancy at OrderLine level. For example:
OrderId OrderLineId DaysReceivedToCompleted 1 1 7 1 2 7
When reporting on an OrderLine for OrderId 1, the DaysReceivedToCompleted is 7 days. When reporting at Order level, the DaysReceivedToCompleted is also 7 days. So this measure is not additive when aggregating from OrderLine to Order level.
But at all levels above Order, this measure is additive, and averages can be calculated.
How can these different types of aggregation for the same measure be defined in Power BI?