Excel to Power Bi Report

[tejapowerbi123]

Hello,

I am trying to convert excel report to power bi report,

1. In excel $ is for constant but in power bi is there any options available?
2. How to write below excel formula in power bi?

=IFERROR(AVERAGEIF('Historical '!$A$3:$X$100687,'Current Year '!$A2,'Historical '!$X$3:$X$100687),"No AUP ")

Can anyone help me?

Thanks

[v-yulgu-msft · Accepted solution]

Hi @tejapowerbi123,

There is no such a corresponding symbol that works like $ to specify a constant cell value in Power BI. We should specify a column name and hardcode the actual value inside a cell in DAX formula. The above excel formula can be converted to DAX similar to below:

New Column =
IFERROR (
    CALCULATE (
        AVERAGE ( TableName[Value] ),
        FILTER ( TableName, <"Filter Expression"> )
    ),
    "No AUP"
)

You should replace the underlined part with the actual filter context, like TableName[Column1]="Category1" or TableName[Column1]=TableName[Column2]

For more advice, please share sample data and show us the desired result which you have got in Excel. How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu