Calculate Average with missing data/Excluding error values

[Anonymous]

Hi!

Sorry this is very long but I'm an absolute Power BI beginner and I want to make sure I'm being clear and not missing a vital bit of info! I'm trying to calculate an average in PowerBI using some data I have pulled from my own Excel spreadsheet. The spreadsheet is used to record what kind of issues customers are emailing in with, how long it takes me to respond and resolve issues, etc.

I use the DATEDIF function in the Excel spreadsheet to calculate the number of days it has taken me to resolve an issue from the date of the first email received, and the same again from the date of the first email I send. The problem being that of course I can't enter the date the case is resolved until I have resolved it, so for certain cases it produces the #NUM! error in Excel.

I've applied formatting in the Excel sheet to replace the error with the text 'N/A'. I can't replace it with the number value 0 because I sometimes resolve issues the same day that they are raised, so that would mess up my stats on how many have been resolved same day.

I pulled the data into Power BI and I've created a table that shows 'Days to resolve (1st email)' and then the count of this next to it. When I've tried to create a new measure to calculate the average number of days taken to resolve the cases, I get an error saying 'AVERAGE cannot work with values of type String'.

I'm assuming that the problem I've got here is the formula calculations that have missing values. Because I've replaced the #NUM! error with text, it won't let me convert the whole column to number values, which having had a google looks like that is part of the problem (can't calculate the average of a word haha!). I did try pulling a different version of the spreadsheet that still had the #NUM! errors in. Power BI picked up on the errors, so I then tried leaving the errors in the data and removing the errors, neither of which worked. I also saw that you can replace the errors with a value in the query function, but it has to be a numerical value which, as I've explained, doesn't work for me as it will mess up my stats!

Another message on the forum provided a formula for excluding certain values from the calculation but it didn't work for me, it got confused because the value I tried to input in the formula was 'N/A'.

I did also try using the function AVERAGEA instead of just AVERAGE, which got rid of the error message, but it returned a result of 0, which is definitely incorrect.

Is there any formula I can use to calculate the average for these values whilst excluding those fields that have an error/text instead of numerical values? If so, can anyone please explain in very simple terms for someone who has extremely limited Excel/Power BI knowledge? Thanks in advance!

[HotChilli · Accepted solution]

Yes that's a basic question so I won't be answering that.  You'll need to have a reasonable understanding of filtering (using DAX or powerbi slicers/filter pane) to get anywhere so you might as well start now.

Good luck.

[Anonymous]

Thanks!

[HotChilli]

That's an extensive explanation and it's a familiar issue.  

So what do we know.  We can't average a text column so it won't work to have a column with 5,3,6,"n/a".  That column would have to be text.

You're not happy about replacing the errors in Power Query but you will have to either remove the rows with errors or replace the value with a valid number and find another way to exclude them from the average.   One way to do that would be to add a column e.g. Resolved with a Yes or No.  So that when the average is calculated you can filter out the Resolved = No.   You can replace the the error values with something like 99999 (it doesn't matter, as long as it's a valid number) and set the column type to whole number or decimal depending on what's in it.

Hope that gets you further.

[Anonymous]

Ahhh I see! I have already got a column for Resolved Y/N so this actually should be fairly straightforward then I hope. What formula should I use to filter out 'resolved = no' ? Sorry I know that is a basic question but I want to be sure I use the right one!