Forum Discussion
Problem with Average
The solution that ended up working for my set of data was:
TotalQtyPerWeek =
CALCULATE(
SUM('Table'[Qty on Hand]),
ALLEXCEPT('Table','Table'[Location],'Table'[Date Week],'Table'[Product Name])
)
WeeksCount =
CALCULATE(
DISTINCTCOUNT('Table'[Data Week]),
ALLEXCEPT('Tabke','Table'[Location],'Table'[Product Name])
)
AverageQtyPerWeek =
DIVIDE(
[TotalQtyPerWeek],
[WeeksCount]
)
I was still getting a very weird average, but I ended up adding some extra ALLEXCEPT conditions and it seems to have worked.
DAX
TotalQtyPerWeek =
CALCULATE(
SUM('Table'[Qty on Hand]),
ALLEXCEPT('Table', 'Table'[Location], 'Table'[Data Week],'Table'[Product Name])
)
Create a measure for the number of weeks per location:
DAX
WeeksCount =
CALCULATE(
DISTINCTCOUNT('Table'[Data Week]),
ALLEXCEPT('Table', 'Table'[Location],'Table'[Product Name])
)
thank you
v-menakakotaCommunity Support
Hi BdC2 ,
We really appreciate your efforts and for letting us know the update on the issue.Please continue using fabric community forum for your further assistance.
If this is the solution that has worked for you please accept your reply as solution so as to help other community members who may face similar issue in the future
Thank you for reaching out to us on the Microsoft Fabric Community Forum.The solution that ended up working for my set of data was:
TotalQtyPerWeek =
CALCULATE(
SUM('Table'[Qty on Hand]),
ALLEXCEPT('Table','Table'[Location],'Table'[Date Week],'Table'[Product Name])
)
WeeksCount =
CALCULATE(
DISTINCTCOUNT('Table'[Data Week]),
ALLEXCEPT('Tabke','Table'[Location],'Table'[Product Name])
)
AverageQtyPerWeek =
DIVIDE(
[TotalQtyPerWeek],
[WeeksCount]
)