cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelK Frequent Visitor
Frequent Visitor

HELP: MOVING AVERAGE EXCLUDING ZERO VALUES

Hi All,

 

I am working on creating a moving average but it is ignoring Zero values.

 

Please bare with me as I am new to the software and BI in general.....

 

Sooooooooooo......I am working with a data set which has multiple fields but the two that I am using are as follows:

1) 'reference number' (text field)

2) DATETIME 

 

I transformed the date time field to END OF HOUR so I can start to calculate average amounts of instructions hitting our queues per hour.

 

I then made another column (using the original date time data) and trasnformed that to DATE only (eg: 2016/10/12).

 

I used the below measure to count the distinct amount of days.

Distinct count days = DISTINCTCOUNT(Query1[DATE ])

 

Then I created a column and measure to count and then sum the reference numbers (used a column as DAX wouldn't alow me to sum a measure).

Column and measure are as follows.

COLUMN: Count REF NO = CALCULATE(COUNTROWS(Query1))    --I didn't use any where clause/filter for this as every line item has                                                                                                          to have a reference number

MEASURE: sum ref no = SUM(Query1[Count REF NO])

 

 

Then for my final step.....

The below measure was created to divide the Sum of my reference numbers by the distinct count of days.

 

CALCULATE(DIVIDE([sum ref no],[distinct count DAYS]))

 

The above measure has given me my moving average when it is plotted over my time field.

 

Everything works fine BUT it is ignoring Zero values and therefore overstating my averages.

 

For example if there were no instructions received on a day between a certain time, it will remove a day from my Distinct Count of Days.

Therefore the 0 value is not included in teh calculation of my average and my average is now larger than what it should be.

 

 

How can I calculate a moving average without Power BI ignoring Zero values?

 

Thank you!

 

1 ACCEPTED SOLUTION

Accepted Solutions
BhaveshPatel Super Contributor
Super Contributor

Re: HELP: MOVING AVERAGE EXCLUDING ZERO VALUES

Check this out. This will help you to answer your question.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
2 REPLIES 2
BhaveshPatel Super Contributor
Super Contributor

Re: HELP: MOVING AVERAGE EXCLUDING ZERO VALUES

Check this out. This will help you to answer your question.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
JustineTennent Frequent Visitor
Frequent Visitor

Re: HELP: MOVING AVERAGE EXCLUDING ZERO VALUES

i used the quick measure to do the rolling average too but have the same problem..did you end up getting an easy solution?

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 285 members 2,983 guests
Please welcome our newest community members: