Search code examples
ssasmdxcube

How measure aggregation can be restricted to single dimension in calculated member


I have the following query where calculated measure TotalPNLPercent shows the % pnl of indivisual fund.

With 
Member [Measures].[Fund_NAV] as
([Measures].[NAV_Calculated_IncludingAccruals_CurrentDay],[Fact PNL Data].[Fund].CurrentMember)

Member [Measures].[TotalPNLPercent]
as (
 Case 
    when isempty([Measures].[Fund_NAV]) or [Measures].[Fund_NAV] = 0
    then 0
    else
    ([Measures].[Total MTM PNL] / [Measures].[Fund_NAV])*100
 End
)
select
non empty
{
    [Measures].[TotalPNLPercent],
    [Measures].[Total MTM PNL],
    [Measures].[Fund_NAV]
} on 0,
non empty
{
    [Fact PNL Data].[Fund].[Fund].Members *
//  [Fact PNL Data].[Asset].[Asset].Members*
    [Fact PNL Data].[Rundate].&[2015-02-11T00:00:00]
} on 1
from 
[DSV_NirvanaClientDW]

When i also put the Asset dimension in the rows axis, [Measures].[Fund_NAV] further splits the number based on the Assets present within a single fund. What i want is [Measures].[Fund_NAV] should not split up other than the funds. If fund1 have 3 assets then same [Measures].[Fund_NAV] should appear corresponding to fund1 and 3 different rows of the assets. Current behavior is fully logical as Fact table is related to the both the fund and asset dimension. I don't want to remove the relationship between the Fact table and asset. Is there any way by which we can restrict the aggregation of the [Measures].[Fund_NAV] calculated measure to the Fund dimension only??


Solution

  • If I understand correctly, you would need to tweak you calcualted member's definition a bit.

    With 
    Member [Measures].[Fund_NAV] as
    (
      [Measures].[NAV_Calculated_IncludingAccruals_CurrentDay],
      [Fact PNL Data].[Fund].CurrentMember,
      [Fact PNL Data].[Asset].[All]
    )
    

    Adding the [All] member explicitly would overwrite the implicit reference to the current member from [Fact PNL Data].[Asset] hierarchy.