Forum Discussion
Problem with Average
I am quite new to PowerBI and really struggling with something I feel should be quite simple... Average...
I have a set of data that has location, product name, Qty and Data Week. The normal Average function causes that ALL quatities are added and devided by the number of products, but I need an average of total quantity per location, per data week, regardless of the number of products in the list.
I need th average to be based on the total quantity for each week. because I have various products in each data week, it is dividing by the number of lines and not the number of weeks, so essentially the total quantity needs to be divided in the below case by 2 (Data Week 1 and 2 = 2) and not the total number of lines. If that makes sense?
| Location | Product Name | Qty on Hand | Data Week |
| A | Product 1 | 200 | 1 |
| A | Product 2 | 300 | 1 |
| A | Product 3 | 200 | 1 |
| A | Product 4 | 100 | 1 |
| A | Product 1 | 100 | 2 |
| A | Product 2 | 300 | 2 |
| A | Product 3 | 300 | 2 |
| A | Product 4 | 100 | 2 |
| B | Product 1 | 50 | 1 |
| B | Product 2 | 200 | 1 |
| B | Product 3 | 300 | 1 |
| B | Product 4 | 500 | 1 |
| B | Product 1 | 200 | 2 |
| B | Product 2 | 600 | 2 |
| B | Product 3 | 200 | 2 |
| B | Product 4 | 300 | 2 |
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]
)
4 Replies
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
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]
)
BdC2 Create a measure for total quantity per location per week:
DAX
TotalQtyPerWeek =
CALCULATE(
SUM('Table'[Qty on Hand]),
ALLEXCEPT('Table', 'Table'[Location], 'Table'[Data Week])
)Create a measure for the number of weeks per location:
DAX
WeeksCount =
CALCULATE(
DISTINCTCOUNT('Table'[Data Week]),
ALLEXCEPT('Table', 'Table'[Location])
)Create the final measure for the average quantity per location per week:
DAX
AverageQtyPerWeek =
DIVIDE(
[TotalQtyPerWeek],
[WeeksCount]
)
