Forum Discussion

sebasjun's avatar
sebasjun
Icon for Helper I rankHelper I
9 years ago
Solved

Sum Accummulate with 2 columns

Hello   Im need to create a accumlated column grouped by 2 criteria. In my case group by A_Codi and Month       Im create a measure with this formula    Acumulated = CALCULATE(sum(Query1[T...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi sebasjun,

    For your result, the cumulated measure is still not sure based on my understanding. For the following example, 54805107<60963705, it shoud be running total.



    You can create a calculated column using the formula, transfer the month text type to number type.

    New-Month=SWITCH([Month], "January", 1,"February",2,  "March", 3, "April", 4,
                           "May", 5, "June",6,  "July",7, "August", 8 ,
                           "September", 9 , "October", 10,"November",11, "December", 
                           12, 0 )

    Then create a measure using the formula.

     Acumulated = CALCULATE(sum(Query1[Total_USD]);FILTER(ALLEXCEPT(Query1,Query1[A_Codi]);Query1[New-Month]<max(Query1[New-Month])))


    I test it using my sample table below, it works fine.



    Create a measure:

    Acumulated = CALCULATE(SUM('FACT'[REVENUE]),FILTER(ALLEXCEPT('FACT','FACT'[ID]),'FACT'[Month]<=MAX('FACT'[Month])))


    Please see the result shown in the following screenshot.



    Best Regards,
    Angelia

MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
9 years ago

What other columns do you have in your table?  Do you have a month number? Or do you have a calendar table?  If you have a month number you can use The same formula you wrote but replace [Mes] with [month number]

  • sebasjun's avatar
    sebasjun
    Icon for Helper I rankHelper I
    9 years ago

    Hello

     

    Thanks is very strange!

     

    I have this model

     

     

     

    The table customer is related with Query 1. And I use the table Customer in all report with other graphs

     

    If I use the A_Codi from query. The table and the graph show correctly

     

     

     

    But use the a_codi from customer table, the table show correctly but the graph no!! 

     

    Don't understand

     

     

    Thanks!

    • MattAllington's avatar
      MattAllington
      Icon for Community Champion rankCommunity Champion
      9 years ago

      Sorry, I can't help you with the information you have provided.  If you post a sample workbook set up and clearly showing the issue, then I will take a look for you

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi sebasjun,

      For your result, the cumulated measure is still not sure based on my understanding. For the following example, 54805107<60963705, it shoud be running total.



      You can create a calculated column using the formula, transfer the month text type to number type.

      New-Month=SWITCH([Month], "January", 1,"February",2,  "March", 3, "April", 4,
                           "May", 5, "June",6,  "July",7, "August", 8 ,
                           "September", 9 , "October", 10,"November",11, "December", 
                           12, 0 )

      Then create a measure using the formula.

       Acumulated = CALCULATE(sum(Query1[Total_USD]);FILTER(ALLEXCEPT(Query1,Query1[A_Codi]);Query1[New-Month]<max(Query1[New-Month])))


      I test it using my sample table below, it works fine.



      Create a measure:

      Acumulated = CALCULATE(SUM('FACT'[REVENUE]),FILTER(ALLEXCEPT('FACT','FACT'[ID]),'FACT'[Month]<=MAX('FACT'[Month])))


      Please see the result shown in the following screenshot.



      Best Regards,
      Angelia

      • sebasjun's avatar
        sebasjun
        Icon for Helper I rankHelper I
        9 years ago

        Thanks this is the problem. 

         

        Best regards!!