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 dashboard data by day, month or quarter. I want to add a 3 day moving average line in the events bar chart. The 3 day moving average is independent of the slicer settings. How can I use a new measure to calculate the 3 day moving average with a DAX formula?

 

Below a simplified example table:

Date, Events

4/26/2017, Event A

4/26/2017, Event A 

4/26/2017, Event B

4/26/2017, Event C

4/26/2017, Event D

4/27/2017, Event A 

4/27/2017, Event B

4/27/2017, Event C

4/28/2017, Event A

4/28/2017, Event A 

4/28/2017, Event B

4/28/2017, Event C

4/28/2017, Event D

4/29/2017, Event B

4/29/2017, Event B

4/30/2017, Event A

4/30/2017, Event B 

4/30/2017, Event C

The expected result are as follows.

The number of unique events for 4/26/2017=4 (Events A, B, C, D), 4/27/2017=3, 4/28/2017=4, 4/29/2017=1, etc.

The 3 day moving average on 4/28/2017 = (4+3+4)/3=3.67 events, on 4/29/2017=(3+4+1)/3=2.67 events.

 

I tried the following but it did not return the desired result:

 

3_Day_Moving_Average = CALCULATE(AVERAGEX(Table, DISTINCTCOUNT(Table[Events])), 
DATESINPERIOD(Table[Date], LASTDATE(Table[Date]), -3, DAY))

 

Thanks.

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,

3 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft 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