Forum Discussion
Using AVERAGEX ON SUMMARIZE DATA
Hi,
PBI file attached.
Hope this helps.
Hi 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 ALLEXCEPT
It'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)