I have a cube in SSAS it has different dimensions and one fact table. one of the dimensions is a dimGoodsType with [weight] attribute. I have a factSoldItems which has [price] measure. now I want to calculate this sum(price * weight) (each solditem has its dimGoodsTypeId so it has its weight related to GoodsType) How can I define this formula in mdx?
1 Answers
0
votes
You can define another measure group in you cube with dimGoodsType as data source table and Weight column as a measure, and connect it with Goods Type dimension as usual. Then, in the properties tab of Price measure you can set Measure Expression as [Measures].[Price] * [Measures].[Weight]. This calculation will take place before any aggregation takes place. The main problem is that if you define straight forward calculation as Price * Weight, SSAS will first sum all weights and sum all prices in the context of the current cell, and only after that it will perform multiplication, but you want to always do your multiplication on the leaf level and to sum from there.
The other solution could be to create view_factSoldItems where you will add your calculated column Weighted Price as price * weight and then add this measure to the cube.
