Search code examples
powerbidaxaverage

How to calculate Average of a column while ignoring the error in PowerBI?

enter image description here

enter image description here

Hi everyone,

I have a table in PowerBI as shown in the first screenshot above. I want to calculate the average WA (Weighted Average) for both Topic 1 and 2. After obtain the average value, then I want to plot the value in the column chart together with the weighted average for Topic 1 and 2 as shown in the second screenshot. The weighted average for Topic 1 is 0.43 while for Topic 2 is 0.50, hence the Average for both Topic 1 and Topic 2 should be 0.465 ~ 0.47.

One of the challenging here is that my data consist of error NaN, so somehow need to ignore the NaN when doing the calculation for Average value.

I'm still new to DAX, hope to get some help and advice on this problem, thank you!

Solution

  • Can you please try with some DAX code as below-

    AVERAGEX(filter(Table, WA <> "NaN"), Table[WA])