cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jlum
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
Moderator 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,

View solution in original post

3 REPLIES 3
Moderator 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,

View solution in original post

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

hema_cherupalli
New Member

Re: Moving average over non-numeric values

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

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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.

Top Solution Authors
Top Kudoed Authors