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.