Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Cumulative Sum Measure

Hi all,

 

I'm struggling with creating a cumulative sum in Power BI desktop.  The cumulative sum would be used to figure out a buying schedule for one of our internal buyers here.  So the fields within the table are:

Date / Purchase Order / Item Number / Quantity

 

So with each change in Purchase Order/Item Number, I need to calculate the cumulative sum of the Quantity field.

 

How can I do this?  I've tried the measure below, but this isn't working - nothing I've found online is working for this.  PRODPURCH is the name of the table I'm using.  Thanks in advance!

MEASURE =
CALCULATE (SUM ( PRODPURCH[QUANTITY] ),
FILTER (ALL ( PRODPURCH ),
PRODPURCH[Date] <= SELECTEDVALUE ( PRODPURCH[Date] )))
Need Help

3 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

    Based on my test, you could refer to below formula:

    Measure = CALCULATE(SUM(PRODPURCH[Quantity]),FILTER(ALL(PRODPURCH),'PRODPURCH'[Item number]<=MAX('PRODPURCH'[Item number])))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Daniel,

       

      Thank you for the response, but unfortunately this did not work, please see the attached image.  

       

      At each change in quantity, I'd expect the 'Measure' measure to change along with it, but it does not.  Can you please advise?

       

      Thanks, Gary

      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        At each change in quantity, I'd expect the 'Measure' measure to change along with it

        What could you mean it? Could you please offer me your sample data to have a test?

         

        Regards,

        Daniel He