Search code examples
mdx

wrong Total using case-when in MDX

If I use the following Negative total instead of previous one i am getting the same output but still not my expected output. Is there anyone to help me out? 

MEMBER [Measures].[Negative Total] AS
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 0
END

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 original value -43380.4 +18 - 9181.32= -52542.81 that was supposed to be 43380.4 +0 +9181.32= 52561.72. For comparing the original total with expected total I displayed [Measure].[Total] and [Measure].[Negative Total] concurrently. Help me please if you know the trickenter image description here

WITH
MEMBER [Measures].[Negative Total] 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 0
END

SELECT
 {
 [Measures].[Total],[Measures].[Negative Total]} on COLUMNS
 , 
NON EMPTY 
 (
    {
        (
        [Dim Account].[HierarchyMarketing].[Account Marketing].&[Income]&[Marketing] 
        *
        {
        [Dim Account].[Account Activity].[Account Activity].MEMBERS,
        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]
Solution

  • It is working as expected since [All] too is a member.

    To achieve what you want, add a base measure that gets the negative total and then use it in the final calculation. 

    MEMBER [Measures].[Negative Total Test] AS
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 0
END

MEMBER [Measures].[Negative Total] AS
CASE
    WHEN [Dim Account].[Account Activity].CURRENTMEMBER IS  [Dim Account].[Account Activity].[All]
    SUM(
        [Dim Account].[Account Activity].[Account Activity].MEMBERS,
        [Measures].[Negative Total Test]
       )
   ELSE [Measures].[Negative Total Test]
END