CODEWITHSUNDEEP
ssasmdx
cairnz
cairnz

Reputation: 3957

Running total only covering range of cells with data

Using the illustrated mockup

(Note not all DimB members show so the total for DimA is larger than the sum of the displayed DimB, ignore this):

Query is showing (months,revenue) on 0, (dima,dimb) on 1 from cube enter image description here

I would like some assistance in creating the calculated member that would calculate the running total across "Months" here (not a real time dimension).

Earlier i was using this one, but it was wrong on so many levels:

[Measures].[Accumulated Revenue] = 
IIF(
        ([DimMonths].[Months].CurrentMember,[Measures].[Revenue]) = 0
    AND ([DimMonths].[Months].CurrentMember.NextMember,[Measures].[Revenue]) = 0
    ,
    IIF(
        AGGREGATE({
            [DimMonths].[Months].CurrentMember :
            [DimMonths].[Months].Parent.LastChild 
        },[Measures].[Revenue]) = 0
        ,
        NULL
        ,
        Aggregate( {
            [DimMonths].[Months].Parent.FirstChild : 
            [DimMonths].[Months].CurrentMember.PrevMember
        }
        ,[Measures].[Revenue])
        )
    ,
    Aggregate( {
        [DimMonths].[Months].Parent.FirstChild : 
        [DimMonths].[Months].CurrentMember
        }
    ,[Measures].[Revenue])
)

Upvotes: 1

Views: 1042

Answers (1)

Bill Anton
Bill Anton

Reputation: 2970

Sounds like a job for a "scoped assignment". Basically, using a scoped assignment, you can overwrite the value of your calculated measure along the [Member_DimA_01] slice so that these values represent a running total instead of a sum of children.

Here's a good tutorial from Chris Webb on scoped assignments to help get you started...

Edit: here's a template for the running total calculation...

WITH 
SET [Months] AS
    {
        [Date].[Calendar Month].&[2011 - Jan]
        :
        [Date].[Calendar Month].&[2011 - Dec]
    }
MEMBER [Measures].[Running Total] AS
    SUM(
         {
            [Date].[Calendar Hierarchy].[Calendar Month].&[2011 - Jan]
            :
            [Date].[Calendar Hierarchy].CurrentMember
         }
        ,[Measures].[Revenue]
    )
SELECT  
    NON EMPTY{
        [Months]
    } ON 0,
    NON EMPTY{
        [DimA].[Member].Allmembers *
        [DimB].[Member].Allmembers
    } ON 1
FROM
    [<<cube name>>]
WHERE
    [Measures].[Revenue Running Total]

Upvotes: 2

Related Questions