Create Calculate Measure Based On One Dimension And 2 Relations In SSAS

Question

Please Consider this Fact Table:

Sender           Receiver         Amount
----------------------------------------
Reseller1        Resseler2        100 
Reseller1        Resseler3        200
Reseller3        Resseler1        150
Reseller3        Resseler2        50
Reseller1        Resseler4        100
Reseller4        Resseler2        350
Reseller4        Resseler1        500
Reseller4        Resseler3        250

I want create a calculate Measures based on this formula: Sum(Receiver)-Sum(Sender) and create such this result:

Reseller             Amount
---------------------------
Reseller1             250    <---- (500 + 150) - (100 + 200 + 100)
Reseller2             500    <---- (100 + 50 + 350) - (0)
Reseller3             250    <---- (200 + 250) - (150 + 50)
Reseller4             -1000  <---- (100) - (350 + 500 + 250)

How can I create calculate measure for this?

Thanks

---

My DimReseller is:

ResellerCode            ResellerName
---------------------------------------
1                        Reseller1
2                        Reseller2
3                        Reseller3
4                        Reseller4

and it has two relationship with Sender and Receiver

---

Edit 1)

Based on Master @Saeid Yousefi's answer:

My structure is:

I wrote this MDX query:

WITH 
MEMBER SenderAmount AS
    [Measures].[Amount], FORMAT_STRING = "Standard"

MEMBER ReceiverAmount AS
     (LinkMember   
     ([Sender].[Hierarchy].CurrentMember, [Receiver].[Hierarchy])  
     ,[Measures].[Amount]), FORMAT_STRING = "Standard"

MEMBER DiffAmount AS
  ReceiverAmount - SenderAmount, FORMAT_STRING = "Standard"

SELECT    {SenderAmount, ReceiverAmount, DiffAmount} ON 0,
[Sender].[Hierarchy].MEMBERS ON 1
FROM [Reseller Sales]

but I got null for receiver amount:

I know there is a small problem but I can't fix that!

Answer 1

Sometimes in a data warehouse a dimension is connected to a fact table in more than one way, these ways can be created using direct or indirect links between Dim and Fact, therefore we can use a dimension to analyze Fact values in different roles; these dimensions are called Role-Playing Dimensions. When designing a cube, for each role of the dimension, a new instance of dimension will be added to the cube. Given these circumstances, each dimension will analyze the cube and although you can use multiple dimensions in a report but in Members level these dimensions are distinguished. With these said, sometimes we feel the need to select a member to be the representative of all roles of a dimension given the situation, this is when Link Member function jumps in to aid us.

This function takes two arguments:

• Member Expression: An MDX expression which returns a member.
• Hierarchy Expression: An MDX expression which returns a hierarchy.

This function returns the member equivalent to a specified member in a specified hierarchy.

So, your code should be something like following:

WITH 
MEMBER SenderAmount AS
    [Measures].[Amount], FORMAT_STRING = "Standard"

MEMBER ReceiverAmount AS
      (LinkMember   
         ([Sender].[Hierarchy].CurrentMember, [Receiver].[Hierarchy])  
         ,[Measures].[Amount]
         ,[Sender].[Hierarchy].[All]), FORMAT_STRING = "Standard"

MEMBER DiffAmount AS
      ReceiverAmount - SenderAmount, FORMAT_STRING = "Standard"

SELECT    {SenderAmount, ReceiverAmount, DiffAmount} ON 0,
NON EMPTY [Sender].[Hierarchy].MEMBERS ON 1
FROM [Cube]

Please pay attention to LinkMember function which is working on role-playing dimensions. So, [Sender] and [Receiver] are the names of your dimensions which are attached to your cube, you can find the name of those in Dimension Usage tab of your cube. Also, [Hierarchy] is the name of your attribute or standard hierarchy, so according to the screenshot of your dimension configuration, it should be [Dimension Name] + either [Hierarchy].[Reseller Code].members or [Reseller Code].members or [Hierarchy].members depending on what you want.

Answer 2

Sample approach to your task:

1. In DSV - create two tables (as SQL named queries) with Sender - Amount and Receiver - Amount columns. Thus you effectively split Sellers and Receivers data.
2. On Cube - create two measure groups with Sum aggregation on Amount column and linked to DimReseller dim.
3. Create a Calc measure as [Measures].[Receiver Amount] - [Measures].[Sender Amount]