I have a simple Data Cube for computing shared expenditures with one measure Amount and some dimensions and hierarchies but interesting one would be Relationship. It describes who bought something for whom. It's structure is:
I am trying to code a calculation representing debt. For example if I bought something for sharing usage it would be half of 0.5 * Amount. On the other hand, if I bought something for myself it would be 0 * Amount.
As far I tried following calculation:
IIF(
[dimRelationship].[Relationship].currentMember = [dimRelationship].[Relationship].[RelatonshipID].&[MeShared],
[Measures].[Amount]*0.5,
[Measures].[Amount]*0)
It works good only at lowest RelationshipID level. When I roll-up browsered pivot-table it is acting according to else-expression. That is not really surprising because hierarchy's currentMember is not MeShared anymore. Another bad thing that total aggregation work neither - it would be most important as a general summary. Is there any suffix like .LeafMember or something like that which could help me perform this calculation?
Thank you in advance!
Best regards,
Max!
In you fact table add another column, in this column store the "Amount" multiplied by the relationshipID for that particular row. This will address you issue right out the box