Forum Discussion

jayendra's avatar
jayendra
Frequent Visitor
9 years ago
Solved

convert the excel formula into Power BI

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=   

 

FPMWD
P0118
P0220
P0322
P0420
P0520
P0620
P0720
P0820
P0920
P1020
P1120
P1220

 

=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

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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