Forum Discussion

Alessandro_Scal's avatar
Alessandro_Scal
Frequent Visitor
2 years ago
Solved

SUMX + CALCULATE not working

Hi Everyone,

 

I am struggling with the following code that apparently seems quite simple:

 

Test sumx =

        SUMX(

            'Database',

        CALCULATE(

        SUM('Database'[Sales])/SUM('Database'[Quantity]), PREVIOUSMONTH('Calendar'[Date].[Date])

    )

        )

Basically, I'd like to compute the AVG price of the previous month, but I want also the results to be all summed up at every level (Customer/Item no./Business Unit). I know it does not make sense to sum up the AVG price, but this is part of a bigger code that I have already checked.

 

The formula doesn't seem to work, all I get is zeros.. but I am having an hard time figuring out why.. Here below I am attaching a snapshot of the Database I am working on.

 

Thank you!

 

Alessandro

 

 

 

 

 

  • lbendlin's avatar
    lbendlin
    2 years ago
    SUMX of prev Avg = CALCULATE( SUMX( 'Table',[Avg]),DATEADD('Calendar'[Date],-1,MONTH))

9 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • Alessandro_Scal's avatar
      Alessandro_Scal
      Frequent Visitor
      2 years ago

      Thanks, for the advice.. reposting with a proper data structure..

       

      Hi Everyone,

      I am struggling with the following code that apparently seems quite simple:

       
      Test SUMX and CALCULATE =
       SUMX(
          Test,
          CALCULATE(
              SUM(Test[Sales])/SUM(Test[Quantity]),PREVIOUSMONTH(Test[Invoice date].[Date])
          )
       )

       

      Basically, I'd like to compute the AVG price of the previous month, but I want also the results to be all summed up at every level (Customer/Item no./Business Unit). I know it does not make sense to sum up the AVG price, but this is part of a bigger code that I have already checked.

      The formula doesn't seem to work, all I get is zeros.. but I am having an hard time figuring out why.. 

       

      This is the database (first 10 rows):

      Item no.Invoice dateMonthYearInvoice no.Business UnitProduct typeCustomer nameSalesQuantity
      266111/12/20231220233078Business Unit AProduct ACustomer 126.681,7023.405,00
      334609/11/2023820232181Business Unit AProduct ACustomer 29.367,647.391,00
      334628/12/202322023420Business Unit AProduct ACustomer 25.044,344.234,00
      339731/12/202312023134Business Unit AProduct ACustomer 32.528,15921,00
      337213/11/202342023825Business Unit AProduct ACustomer 33.962,091.991,00
      337009/11/2023520231113Business Unit AProduct ACustomer 34.641,393.049,00
      337021/12/202322023380Business Unit AProduct ACustomer 33.909,622.734,00
      210511/11/2023520231158Business Unit AProduct ACustomer 41.534,001.300,00
      210515/12/2023920232249Business Unit AProduct ACustomer 53.555,202.020,00

       

      This is the outcome:

      Any help?!

       

      Thank you!

       

      Alessandro