Forum Discussion

marcss44's avatar
marcss44
Helper I
1 year ago
Solved

Average Over Sum by date

I've got a table with multiple rows by invoices, overyone with invoice quantity and invoice date. I need to sum invoice quantity by date to get something like this: And after that get an uniq...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    marcss44 , Try using

    dax
    SumInvoiceQuantity =
    SUMX(
    SUMMARIZE(
    Table1,
    Table1[Fecha],
    "TotalQuantity", SUM(Table1[Cantidad])
    ),
    [TotalQuantity]
    )

     

     

    If this still doesn't work, you can try breaking it down into two separate measures to make it easier to debug:

    Create a measure to calculate the total quantity per date:

    dax
    TotalQuantityPerDate =
    SUMMARIZE(
    Table1,
    Table1[Fecha],
    "TotalQuantity", SUM(Table1[Cantidad])
    )

     

     

    And one more for 

    dax
    SumInvoiceQuantity =
    SUMX(
    TotalQuantityPerDate,
    [TotalQuantity]
    )

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

marcss44 Create a new measure in Power BI to sum the invoice quantity by date. You can use the SUM function along with GROUP BY to achieve this.

 

DAX
SumInvoiceQuantity =
SUMX(
SUMMARIZE(
'YourTable',
'YourTable'[InvoiceDate],
"TotalQuantity", SUM('YourTable'[InvoiceQuantity])
),
[TotalQuantity]
)

 

Create another measure to calculate the average of the summed quantities.

DAX
AverageOfSumInvoiceQuantity =
AVERAGEX(
SUMMARIZE(
'YourTable',
'YourTable'[InvoiceDate],
"TotalQuantity", SUM('YourTable'[InvoiceQuantity])
),
[TotalQuantity]
)

  • marcss44's avatar
    marcss44
    Helper I
    1 year ago

    I've used for this for the first part:

    SumInvoiceQuantity =
     
    SUMX(
    SUMMARIZE(
    Table1,
    Table1[Fecha],
    "TotalQuantity", SUM(Table1[Cantidad])
    ),
    [TotalQuantity]
    )
     
    And it shows: the expression specified in the query is not a valid table expression
    • bhanu_gautam's avatar
      bhanu_gautam
      Super User
      1 year ago

      marcss44 , Try using

      dax
      SumInvoiceQuantity =
      SUMX(
      SUMMARIZE(
      Table1,
      Table1[Fecha],
      "TotalQuantity", SUM(Table1[Cantidad])
      ),
      [TotalQuantity]
      )

       

       

      If this still doesn't work, you can try breaking it down into two separate measures to make it easier to debug:

      Create a measure to calculate the total quantity per date:

      dax
      TotalQuantityPerDate =
      SUMMARIZE(
      Table1,
      Table1[Fecha],
      "TotalQuantity", SUM(Table1[Cantidad])
      )

       

       

      And one more for 

      dax
      SumInvoiceQuantity =
      SUMX(
      TotalQuantityPerDate,
      [TotalQuantity]
      )