We have a fact table with the weight trasported by truck/driver/day.
And a dimension with the max weight per truck.
We would like to have the % of weight transported, so we started with
create MEMBER CURRENTCUBE.[Measures].[% WT] AS [Measures].[Weight]/[Dim Truck].[Max_weight].currentmember.properties("key"), FORMAT_STRING = "Percent";
This only works for individual rows where one truck has only one leaf for the specific day and driver.
It doesn't work if a truck does two trips in a day, instead of sum(weight)/sum(max_weight) or if you prefer sum(weight)/(n*max_weight) what we get is sum(weight)/max_weight
Exactly the same occurs at any aggregate level. For example, month or year or total. Or simply at truck level.
We have been playing with all sorts of formula and scopes without success.
Any idea how to define it so the aggregates work as expected?