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 unique value of the average of every row.

How can i do it?

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]
    )

4 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Hi marcss44 Try these please 

    • Create a Date-Sum Table:

      • Use SUMMARIZE to group by date and sum the invoice quantities.

     

    SumByDate = 
SUMMARIZE(
    'Table',
    'Table'[Date],
    "TotalQuantity", SUM('Table'[Quantity])
)​

     

    • Calculate the Average of Sums:

       

     

    AverageSumByDate = 
AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[Date],
        "TotalQuantity", SUM('Table'[Quantity])
    ),
    [TotalQuantity]
)

     

    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

     

  • 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]
        )