Forum Discussion

BdC2's avatar
BdC2
New Member
1 year ago
Solved

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...
Need Help
Show and Tell
  • BdC2's avatar
    BdC2
    1 year ago

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

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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