Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Cumulative sum of multiple groups

Hi,

 

I am trying to calculate the cumulative sum per PERIOD_NAME and the sumulative sum per MFG_ORDER_NAME and PERIOD_NAME. Data I have is MFG_ORDER_NAME, PERIOD_NAME and TOTAL_COSTS_PER_JOB_IN_PERIOD. See table. The last column is the value I want to calculate if the cumulative costs per MFG_ORDER_NAME are calculated per period.

 

Can anyone advise which DAX formula(s) this can solve?

 

Thanks in advance.

 

Need Help

4 Replies

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

    Hi Anonymous,

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

    Add index column for your data in query editor:

    And create below calculated column:

    Cumulative sum = CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Index]<=EARLIER(Table1[Index])&&'Table1'[MFG_ORDER_NAME]=EARLIER(Table1[MFG_ORDER_NAME])))

    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,

       

      Unfortunatel I  a working in DirectQuery mode, so insert an index is not supported within direct query.

       

      However I managed to calculate the **bleep**.costs per MFG_ORDER_NAME by following calculation (Period_CHAR recalculated to a date field called PERIOD_MID_DATE)

       
      CUM_COSTS = CALCULATE(SUM(XXBI_WIP_PERIOD_BALANCE_TOT_V[TOTAL_COSTS_PER_JOB_IN_PERIOD]),FILTER(ALLEXCEPT(XXBI_WIP_PERIOD_BALANCE_TOT_V,XXBI_WIP_PERIOD_BALANCE_TOT_V[MFG_ORDER_NAME]),XXBI_WIP_PERIOD_BALANCE_TOT_V[PERIOD_MID_DATE]<=MAX(XXBI_WIP_PERIOD_BALANCE_TOT_V[PERIOD_MID_DATE])))

       

      Printscreen of solution:

       

      Question is the following: When I visualize the total costs of PERIOD_NAME_CHAR, so MFG_ORDER_NAME is not included, the total does not match. Total for December 2018 should be 7789542 (export from Power BI to excel - second printscreen below) while total is represented in Power BI as 8321509. See first printscreen. Why is there a difference and how to solve this?

       

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi, can someone help please!!! Let me know if something is not clear.

         

        Thanks in advance

  • AnnaSA's avatar
    AnnaSA
    Icon for Helper I rankHelper I
    6 years ago

    Hi Anonymous 

    Hope you are doing well.

    I am struggling with a similar problem. Just wanted to find out if you've maybe managed to solve it?

    Kind regards