Let me explain what I am trying to achieve. I have a Dimension of [Dim Account] which has the attributes like [Account Type],[Account Activity], [Account Marketing] etc. [Account Type] is income, expense,.. [Account Activity] is like Advertising,Discounts, Sale of sponsorship,etc [Account Marketing] is the combination of those things I mean advertising is the expense whereas discounts is stored as the negative income, so ultimately discounts will be treated as marketing expense That's why my member [Negative Total] is converting the income multiplied by -1 and i want to exclude those income are positive as they are income. I want to display only those converted income and finally add up them. In my MDX, the data are displayed perfectly but added up the anonymously due to
the condition of [Dim Account].[Account Activity].CURRENTMEMBER IS [Dim Account].[Account Activity].[All] when I wanted to accumulate with original income (650,920.88) then it's coming up with big figure that's not expected. Another solution I need I want to add up marketing expense(24010.44) with negative marketing income which are already converted (43380.4+9181.32) so finally the result should be 76572.16 like my edited picture. Please modify my MDX.
WITH
//MEMBER [Measures].[Negative Total Test] AS -- (1)
//CASE
// WHEN ([Measures].[Total],[Dim Account].[Account Marketing].&[Income]&[Marketing]) < 0
// THEN [Measures].[Total]*-1
// WHEN ([Measures].[Total],[Dim Account].[Account Marketing].&[Income]&[Marketing]) > 0
// THEN NULL
// ELSE
// [Measures].[Total]
//END
MEMBER [Measures].[Negative Total Test] AS
CASE
WHEN
[Dim Account].[Account Marketing].CurrentMember.Member_Caption
= "Marketing"
AND
[Dim Account].[Account Marketing].Properties("Account Type") = "Income"
AND
[Measures].[Total] < 1
THEN
[Measures].[Total] * -1
WHEN
[Dim Account].[Account Marketing].CurrentMember.Member_Caption
= "Marketing"
AND
[Dim Account].[Account Marketing].Properties("Account Type") = "Income"
AND
[Measures].[Total] > 1
THEN NULL
ELSE
[Measures].[Total]
END
MEMBER [Measures].[Negative Total] AS
CASE
WHEN
[Dim Account].[Account Activity].CurrentMember
IS
[Dim Account].[Account Activity].[All]
THEN
Sum
(
[Dim Account].[Account Activity].[Account Activity].MEMBERS
,[Measures].[Negative Total Test]
)
ELSE
[Measures].[Negative Total Test]
END
//MEMBER [Dim Account].[Account Activity].[TOTAL] AS
// [Dim Account].[Account Activity].[All]
MEMBER [Dim Account].[HierarchyMarketing].[Total Marketing Expense] AS
Aggregate
(
{
[Dim Account].[Account Marketing].&[Income]&[Marketing]
,[Dim Account].[Account Marketing].&[Expenses]&[Marketing]
}
,[Measures].[Negative Total Test]
)
SELECT
{
[Measures].[Total]
,[Measures].[Negative Total]
} ON COLUMNS
,NON EMPTY
(
{
[Dim Account].[HierarchyMarketing].[Account Type].&[Income]
*
{VisualTotals([Dim Account].[Account Activity].[All])}
,
{
[Dim Account].[HierarchyMarketing].[Account Marketing].&[Expenses]&[Marketing]
,[Dim Account].[HierarchyMarketing].[Account Marketing].&[Income]&[Marketing]
}
*
[Dim Account].[Account Activity].[Account Activity].MEMBERS
,(
[Dim Account].[HierarchyMarketing].[Total Marketing Expense]
,VisualTotals([Dim Account].[Account Activity].[All])
)
}
,[Dim Branch].[Trading As].&[BAR]&[Barlens Event Hire]
,[Dim Scenario].[Scenario Name].&[Actual]
,[Dim Fiscal Year].[HierarchyFiscal].[E Month].&[2016]&[December]
) ON ROWS
FROM [CubeProfitLoss];
WOW ! I got my answers, that's the thing I was looking for. It was nothing but only changes is the display of [Negative Total Test]. enter image description here
WITH
MEMBER [Measures].[Negative Total Test] AS
CASE
WHEN
[Dim Account].[Account Marketing].CurrentMember.MEMBER_CAPTION="Marketing" AND
[Dim Account].[Account Marketing].Properties("Account Type") ="Income" AND
[Measures].[Total]<1
THEN [Measures].[Total]*-1
WHEN
[Dim Account].[Account Marketing].CurrentMember.MEMBER_CAPTION="Marketing" AND
[Dim Account].[Account Marketing].Properties("Account Type") ="Income" AND
[Measures].[Total]>1
THEN NULL
ELSE
[Measures].[Total]
END
MEMBER [Measures].[Negative Total] AS
CASE
WHEN [Dim Account].[Account Activity].CURRENTMEMBER IS [Dim Account].[Account Activity].[All]
THEN
SUM(
[Dim Account].[Account Activity].[Account Activity].MEMBERS,
[Measures].[Negative Total Test]
)
ELSE [Measures].[Negative Total Test]
END
MEMBER [Dim Account].[HierarchyMarketing].[Income] AS
([Dim Account].[HierarchyMarketing].[Account Type].&[Income], [Measures].[Negative Total])
MEMBER [Dim Account].[HierarchyMarketing].[Total Marketing Expense] AS
AGGREGATE
(
{
[Dim Account].[Account Marketing].&[Income]&[Marketing]
,
[Dim Account].[Account Marketing].&[Expenses]&[Marketing]
}
,
[Measures].[Negative Total]
)
SELECT
{
//[Measures].[Total], [Measures].[Negative Total],
[Measures].[Negative Total Test]
} on COLUMNS
,
NON EMPTY
(
{
(
[Dim Account].[HierarchyMarketing].[Account Type].&[Income]
*
{
[Dim Account].[Account Activity].[All]
}
)
,
(
{
[Dim Account].[HierarchyMarketing].[Account Marketing].&[Expenses]&[Marketing]
,
[Dim Account].[HierarchyMarketing].[Account Marketing].&[Income]&[Marketing]
}
*
[Dim Account].[Account Activity].[Account Activity].MEMBERS
)
,
(
[Dim Account].[HierarchyMarketing].[Total Marketing Expense]
,
[Dim Account].[Account Activity].[All]
)
}
,
// Traders,
[Dim Branch].[Trading As].&[BAR]&[Barlens Event Hire] ,
[Dim Scenario].[Scenario Name].&[Actual] ,
[Dim Fiscal Year].[HierarchyFiscal].[E Month].&[2016]&[December]
)
on ROWS
FROM [CubeProfitLoss]
;