cancel
Showing results for
Did you mean:
Frequent Visitor

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions v-sihou-msft
Moderator

## Re: Moving average over non-numeric values

@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 3 v-sihou-msft
Moderator

## Re: Moving average over non-numeric values

@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,

Frequent Visitor

## Re: Moving average over non-numeric values

@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.

New Member

## Re: Moving average over non-numeric values

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

Announcements #### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community! #### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries. #### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now. #### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities. #### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications Top Solution Authors
Top Kudoed Authors
Users online (697)