I have a cube in SSAS multidimensional mode.
I have created a calculating measure in visual studio called "Total Cost". The formula is:
[Measures].[Unit Cost]*[Measures].[Qty]
It is in the lowest level of granularity (i.e. - the transnational level information has these fields).
The formula works well, as long as I present the data in this same level of granularity (for example, when I create a pivot and the rows are transaction IDs - like the source file)
However, when I present it in an aggregate format (for example - by customer) - then instead of making the calculation and then sum it up, it sum up and then calculate.
Here is what I expected: Expected results vs. What I get
My understanding, that this is regardless a (correct/incorrect) hierarchy structure. In other words, I expected this calculation to work even without defining any hierarchy between the transaction ID level and the customer level.
I'd appreciate your help!
In your SSAS project ->datasource view, you need to add a named calculation. This would be "[Unit Cost]*[Qty]". Now add this named calculation as a Measure in your Cube. This do the job. This problem was already addressed in the following link. https://stackoverflow.com/questions/53554284/how-to-multiply-two-measures-prior-to-aggregation/53558733#53558733
