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

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