Cumulative Sum - What am I missing?

[DarrenLau]

Trying to get a cumulative sum (and then a cumulative percentage) value into a matrix table, but I am just not able to get the correct calculations. Here are 3 different combinations (amongst the many others that I have tried) as examples:-

Example 1:-

Cumulative Disti Net Sales = 
CALCULATE( [Disti Net Sales], 
    FILTER ( 
        ALLSELECTED ( 'Sales Data' ),
        'Sales Data'[Index] <= MAX ( 'Sales Data'[Index] )
    )
)

Example 2

Cumulative Disti Net Sales = 
CALCULATE( [Disti Net Sales], 
    FILTER ( 
        ALLSELECTED ( 'DateDimension' ),
        DateDimension[FullDateAlternateKey] <= MAX ( DateDimension[FullDateAlternateKey] )
    )
)

Example 3

Cumulative Disti Net Sales = 
CALCULATE( [Disti Net Sales], 
    FILTER ( 
        ALLSELECTED ( 'Sales Data' ),
        'Sales Data'[Ship To Cust Id] <= MAX ( 'Sales Data'[Ship To Cust Id] )
    )
)

What am I missing or not doing correctly?

I have successfully created a cumulative measure that maps out sales over time (using the date table & date field), but not sure what I am doing wrong here.

Edit/Addition clarification:-

Disti Net Sales = sum ( 'Sales Data'[Invoice-Doc] )

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Rank = if([Disti Net Sales] = BLANK(),BLANK(),RANKX(ALL('Sales data'[City]),[Disti Net Sales]))

Cumulative Disti Net sales = SUMX(TOPN([Rank],CALCULATETABLE(VALUES('Sales data'[City]),ALL('Sales data'[City])),[Disti Net Sales]),[Disti Net Sales])

Cumulative Disti Net sales (%) = [Cumulative Disti Net sales]/CALCULATE([Cumulative Disti Net sales],all('Sales data'[City]))

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Rank = if([Disti Net Sales] = BLANK(),BLANK(),RANKX(ALL('Sales data'[City]),[Disti Net Sales]))

Cumulative Disti Net sales = SUMX(TOPN([Rank],CALCULATETABLE(VALUES('Sales data'[City]),ALL('Sales data'[City])),[Disti Net Sales]),[Disti Net Sales])

Cumulative Disti Net sales (%) = [Cumulative Disti Net sales]/CALCULATE([Cumulative Disti Net sales],all('Sales data'[City]))

Hope this helps.

[DarrenLau · Accepted solution]

Hi @Ashish_Mathur ,

Thank you for the measures. The cumulative % measure was not working, but I edited it to :-

Cumulative Disti Net Sales % = 
DIVIDE( [Cumulative Disti Net Sales] ,
    CALCULATE(SUM('Sales Data'[Invoice-Doc]), ALL('Sales Data'[City])) , 
    BLANK()
)

And it's showing the cumulative percentages now.

[Ashish_Mathur]

You are welcome.

[DarrenLau]

Bumping up to see if anyone has any thoughts or ideas. Thank you.