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

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