a * b is not aggregated correctly in MS olap cube

[pvdput]

I have a product dimension with levels product group and product code.
I have a measure called quantity and a measure called price.
A calculated measure = quantity * price called value
on produccode level this works fine however on product group level it is calculated as sum(quantity) / sum(price) giving incorrect results.
Does anyone know how to solve this

 

[pabloj]

Solve it in the fact table, it's much easier.

Stick to your guns

 

[MDXer]

Build a view in SQL that has the Column that is ColumnName = quantity * price, along with all the other columns needed for your cube. Another alternative is to build a measure (no calculated) in your cube that on the left pane under the tree view where you have the properties panel you can specify the columns and formula for the measure using the table columns.

For ease of use and performance it is best to do granular level calculations while the cube is processing.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck