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

dimensionmdxssas

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?

Best Answer

Your main problem is that you are storing a measure in your dimension. You will keep running into issues with multiselect and or aggregation unless you store the measure where it belongs, namely in the fact table.

Either change your ETL to copy the max_weight in your fact table, or use a named query in your DMV joining your fact table to your dimension table so you can create a real measure.

Depending on your situation you could also add a new measure group.

Related Question