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??
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.