Sample Header Ad - 728x90

How to calculate a percentage over a dimension attribute and get correct aggregates?

3 votes
2 answers
1061 views
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?
Asked by DeepButi (161 rep)
Aug 31, 2017, 02:05 PM
Last activity: Sep 5, 2017, 01:51 PM