Different aggregations for different measures in the same cube - 03-21-2005 , 02:40 PM
I hope that someone has had the same problem and found the answer.
I have a fact table with sales data, having Tons sold and prices and costs
(I need to have a calculated measure showing the margin per
product-customer-time). The resulting cube should show Tons of sales by
product-client by time. The point is that Tons are naturally summarized
(that is OK), as well as prices and costs. Prices and Costos should be
averaged, since it does not make any sense to summarize them by product,
client or product-client.
I have tried with custom rollup formulas, but I must do this for every one
of the three dimensions-levels revelant to the slice or view, making the
cube really slow and finally get an "error" in every cell
Is there any way to summarize Tons but get the average for costos and
Moreover, could be those averages "Weigheted Averages" based on the portions
of those Tons in the total for that product-customer?
Is something I should solve at the fact table level? Or something that can
be programmed in MDX and the cube level?
Any help will be highly appreciated.