Forum Discussion

DarrenLau's avatar
DarrenLau
Advocate I
7 years ago
Solved

Cumulative Sum - What am I missing?

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] )
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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's avatar
    DarrenLau
    7 years ago

    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.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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's avatar
      DarrenLau
      Advocate I
      7 years ago

      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.

  • DarrenLau's avatar
    DarrenLau
    Advocate I
    7 years ago

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