cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

Join THE global Microsoft Power Platform event series.

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

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