Forum Discussion
Using AVERAGEX ON SUMMARIZE DATA
Hi,
PBI file attached.
Hope this helps.
Super UserHi Anonymous - You're on the right track, but the issue arises because AVERAGEX does not recognize [Total Qty] as a valid column within SUMMARIZE.
SALES BY LOCATION =
VAR SalesSummary =
SUMMARIZE(
Sheet1,
Sheet1[Item],
Sheet1[LOCATION],
Sheet1[Date].[Month],
"Total Qty", SUM(Sheet1[QTY])
)
RETURN
ADDCOLUMNS(
SalesSummary,
"Avg Monthly Qty",
CALCULATE(AVERAGEX(SalesSummary, [Total Qty]),
ALLEXCEPT(SalesSummary, Sheet1[Item], Sheet1[LOCATION]))
)
This should give you a column with the same average monthly quantity for all months of a given location and item.
Hope this helps.
Hi rajendraongole1
Thanks for your assitance with this. I tried to use your formula and understand what it is trying to achieve. However I am getting this error message when using ALLEXCEPTIt's as if the VAR SalesSummary isn't recognised as a table that is expected with ALLEXCEPT
Here is the dataset I am using:
LOCATION Item QTY SALES Date month A K110 2 200 01/02/2025 2 B K110 5 500 01/01/2025 1 A K110 3 310 05/01/2025 1 A K110 15 1500 08/03/2025 3 B K110 2 4000 01/02/2025 2 B K110 10 1000 09/02/2025 2 B K110 5 500 07/03/2025 3 B K200 4 335 06/02/2025 2 A K200 4 335 04/02/2025 2 B K200 8 640 06/03/2025 3 A K110 10 1200 12/03/2025 3 A K200 6 487 15/02/2025 2 B K200 7 572 28/02/2025 2 Thank you for you looking into this.
Just to be clear. I would expect the average qty of item K110 at location A to be 10 (2+3+15+10)/ 3 ( this data covers 3 months)