How to write excel formula in Power BI?

[tejapowerbi123]

Hello,
How to write below excel formula into Power BI?
=IFERROR(AVERAGEIF('Historical '!$A$3:$X$100687,'Current Year '!$A2,'Historical '!$X$3:$X$100687),"No AUP ")

Historical $A$3 is table,current year and Historical $x$3 are column.I am not sure do i need also need lookup as well.Thanks

[v-juanli-msft]

Hi @tejapowerbi123

I use the formula in Excel, it throws an error as below

=AVERAGEIF($A$3:$B$9,$A2,$B$3:$B$9)

According to reference here: AVERAGEIF(range, criteria, [average_range])

the "criteria" in your formula is not clear.

In power bi, similar formula as above is 

AVERAGE = CALCULATE(AVERAGE(Historical[Historical]),FILTER(ALL(Historical),[Current Year]=2016))

IFERROR = IFERROR([AVERAGE],"No AUP")

Best Regards

Maggie