Hi Guys,
I have unique Requirement my database has following things:-
Dimensions
Activity (Each Item belongs to Activity)
Item
Measures
Quantity - 2005
Quantity - 2006
Total Invoice Value - 2005
Total Invoice Value - 2006
Calculated Measure
Delta Price (Difference Between 2005 & 2006 Price)
Price - 2005 (Value/Quantity)
Price - 2006
Basically how it happened is Price was calcuated at PartNumber and then at Activity level. My finance Department now wants calculation of Delta Price for Activity from PartNumber i.e.
Quantity - 2005 * Price - 2005 * Delta Price at (Part Number Level) Sum this and Divided by Value - 2005
I am giving following definition in calculated member -
Sum({[Part Number]},[Measures].[Quantity 2005] * [Measures].[Net Net Unit Price AED 2005] *[Measures].[Delta Net Net Unit Price - AED])
But what this does is again takes value at Activity level and does the calculation, can somebody help me to achieve this
Regards,
Kaushal
Hi Kaushal. I'd like to help, but Ithink I need more information first. If you can answer a few questions I think we can help.
(1) What is the structure of your Activity dimension? Are Part Numbers in the Activity dimension? Perhaps you can provide a brief example?
(2) Your calculated member definition states: "...Sum this and Divided by Value - 2005". Yet, your calculated member doesn't have the division operator (/). Does the calculated member need the division?
(3) Please provide a brief example of the "wrong" answer you get, and the "correct" answer you expect.
Thanks.
PGoldy
|||(1) What is the structure of your Activity dimension? Are Part Numbers in the Activity dimension? Perhaps you can provide a brief example?
Yes PartNumber is part of Activity Dimension
(2) Your calculated member definition states: "...Sum this and Divided by Value - 2005". Yet, your calculated member doesn't have the division operator (/). Does the calculated member need the division?
Yes
(3) Please provide a brief example of the "wrong" answer you get, and the "correct" answer you expect.
Regards,
Kaushal
|||Your issue appears to be that you are getting a product of sums, not a sum of the products.
To force the calculation to do the product first and then sum up you would do something like the following:
Sum(
Descendants([Activity].CurrentMember,[Activity].[Part Number])
,[Measures].[Quantity 2005] * [Measures].[Net Net Unit Price AED 2005] *[Measures].[Delta Net Net Unit Price - AED]
)
This will effectively get all the relevant part numbers, perform the calculation and then sum up the results of the calculation.
|||Thanks Darren - I was a little behind following up.
PGoldy
|||Thanks Darren,
It was great help, can you tell me if Part Number is not a level of Activity can i apply the same formula in someother way.
Regards,
Kaushal
|||
Paul: No worries, I think it is one of the strengths of these forums in that we can all chip in and contribute :)
Kaushal: You could probably apply a similar technique, but maybe not that exact formula if Part Number was on a different dimension. It really depends on how you want the logic to work and how your cube is structured. It could be as simple as replacing the references to the Activity dimension or it could get more complicated. I really don't have enough information to be able to answer that question fully.
If you are using SSAS 2005, you have a couple of other options open to you in addition to doing a straight calculated measure. One approach which would probably be ideal in this situation is to use a Measure Expression, these expressions get evaluated at the fact table grain and then get aggregated up and are very fast.
If your logic gets a bit more complicated another approach in SSAS 2005 could be to use a scoped assignment, this is very similar to a straight calculated measure, but potentially more efficient as you can restrict the subcube over which the calculation is perform.
No comments:
Post a Comment