Average Over Sum by date

[marcss44]

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?

[bhanu_gautam]

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

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 · Accepted solution]

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

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