convert the excel formula into Power BI

[jayendra]

Hi

I have  excel data and i moved to sharepoint online list after i get the data into Power Bi.And i have to generate the report based on formulas.And here  1 static sheet is their it has same constant values are their like this

static sheet

 here the actual details sheet

And here is the excel formula 

AE7= Total Actual MWD FP

$C7=FP Cost Name

MWD_AD=   

FP	MWD
P01	18
P02	20
P03	22
P04	20
P05	20
P06	20
P07	20
P08	20
P09	20
P10	20
P11	20
P12	20

=IFNA(AE7/(VLOOKUP($C7,MWD_AD,2,FALSE)),"FP Not in Range")

How  can i slove this excel formula into Power Bi.Please can you help ASAP

Regards

jayendra

[Anonymous · Accepted solution]

Hi @jayendra,

Perhaps you can try to use below formula:

Test=
var Current_FP=LOOKUPVALUE(FP[FP],FP[ID],2)
var Current_MWD=LOOKUPVALUE(MWDTable[MWD],MWDTable[FP],Current_FP)
return
IF(ISERROR(detail[Total Actual MWD FP]/Current_MWD),"FP Not in Range",detail[Total Actual MWD FP]/Current_MWD)

In addition, Since I'm not very clear for your table names and structures, can you please share a sample pbix file which contains these tables and relationships?

Regards,

Xiaoxin Sheng