Forum Discussion

jlum's avatar
jlum
Frequent Visitor
9 years ago
Solved

Moving average over non-numeric values

Hi, I'm trying to create a measure for calculating a 3 day moving average of unique events of string type.   On the dashboard there is a timeline slicer for date, so the user can visualize the dash...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    jlum

     

    In this scenario, since you need to get the distinct count of event on each day and calculate average, I suggest you summarize a new table to with distict count calculated group on day level.

     

    Daily Events=
SUMMARIZE (
    Table,
    Table[Date],
    "Daily Distinct Count Events", CALCULATE ( DISTINCTCOUNT ( Table[Events] ), ALLEXCEPT ( Table, Table[Date] ) )
)

    Then you can calculate the moving average based on above calculated table.

     

     

    Regards,

v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

jlum

 

In this scenario, since you need to get the distinct count of event on each day and calculate average, I suggest you summarize a new table to with distict count calculated group on day level.

 

Daily Events=
SUMMARIZE (
    Table,
    Table[Date],
    "Daily Distinct Count Events", CALCULATE ( DISTINCTCOUNT ( Table[Events] ), ALLEXCEPT ( Table, Table[Date] ) )
)

Then you can calculate the moving average based on above calculated table.

 

 

Regards,

  • jlum's avatar
    jlum
    Frequent Visitor
    9 years ago

    v-sihou-msft

     

    I was hoping there was a way to calculate the moving average without creating a new table.

    I ended up doing what you suggested.

    Thanks. 

  • hema_cherupalli's avatar
    hema_cherupalli
    New Member
    8 years ago

    Hi v-sihou-msft

     

    I also bumped into the same issue of  calculating the "Moving average over non-numeric values"

     

    And tried your solution but getting the following error:

     

    "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

    I wonder what does this actually mean?

     

     

    Any help is much appreciated.

    Thanks