Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Using averages with Time

Im having trouble calculating averages such as: average # of users per hour, average # of users per day, average quantity per hour, average to LPNs per hour. I am currently using this formula but am getting unreal results back:

 

Distinct Operator average per Day =
AVERAGEX(
    KEEPFILTERS(VALUES('2022ChalkHillPickerData'[Date])),
    CALCULATE(DISTINCTCOUNT('2022ChalkHillPickerData'[User])
))

 

Any help would be greatly appreciated.

 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Try:

    Distinct Operator average per Day =
    VAR __Table = 
      SUMMARIZE(
        '2022ChalkHillPickerData',
        [Date],
        "Value",COUNTROWS(DISTINCT('2022ChalkHillPickerData'[User]))
      )
RETURN
    AVERAGEX(__Table, [Value])
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Do think that same concept will work for time based calculations as well?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Using the same formula, but adapted to time and looks like this:

       

      Distinct Operator average per Hr =
      AVERAGEX(
          KEEPFILTERS(VALUES('2022ChalkHillPickerData'[Time])),
          CALCULATE(distinctcount('2022ChalkHillPickerData'[User]))
      )