Exclude values with text in cells for average

[nlsantan]

Hello,

I'm trying to build a dashboard in Power BI that will tell me average Turnaround times for events (TATS as named in my workbook), and my data is set to where if the TAT cannot be calculated due to an empty field or a negative value (TATs can't be negative unless you are into time travel), the value in the cell populates with the text "Null". 

I do realize that I can eliminate the "Null" values and leave blank, and have the Calculation to exclude blanks, however, I want to leave the "Null" Values in there to determine the percent of errors in our data as well. Also, for certain TATS, the actual TAT is Zero, for example if the exam is ordered at the same time as the patient is admitted. So excluding all zeroes would throw off my average.

So for this exercise, I would like to calculate the average Admit to Order Time based on the values in Column B In the attached file. However the "Null" values cause an error in the calculation and will not return an average time. 

What is a specific measure that I can create to ignore the "Null" values and calculate an average TAT based only on numerical values?

Any help will be greatly appreciated!

[v-juanli-msft]

Hi @nlsantan

edhans's suggestion is correct, you could consider DAX function "Average" for ignore "null" value.

But I have puzzle what expected result you want.

Is this your expected results? (based on your words : calculate the average Admit to Order Time based on the values in Column B)

Best Regards

Maggie

[edhans]

I just built a measure that is simply below and it works. AVERAGE ignores text, blanks, nulls. You'd have to use AVERAGEA to have it count text/non-numerics.

=AVERAGE(TestData[Amount])

I even typed the word NULL in there (which is just arbitrary text) as Power Query would return blanks for nulls.

So based on your image, I cannot get AVERAGE() to fail. It doesn't include them in the numerator or denominator of its calculations. Totally ignores them.