Forum Discussion

Gpippin8's avatar
Gpippin8
New Member
8 years ago

PowerPivot not returning the the correct value?

I'm writing a simple IF formula and powerpivot is not returning the correct value.

Formula: =RELATED(CostCenterLookup[Adjusted Cost Center])

I'm using a related table that will not have a value for all lines in the current table so this returns values (number format) and blanks.

When there is a blank, I want to get the value in the current table so I've adjust the formula to:

=IF(RELATED(CostCenterLookup[Adjusted Cost Center])=BLANK(),CustomerMargin[CostCenter],RELATED(CostCenterLookup[Adjusted Cost Center]))

For some reason this returns the same answer as the original formula? Either the value in the Related table or a blank.

If I add the IF statement again, it corrects the issue:

=IF(IF(RELATED(CostCenterLookup[Adjusted Cost Center])=BLANK(),CustomerMargin[CostCenter],RELATED(CostCenterLookup[Adjusted Cost Center]))=BLANK(),CustomerMargin[CostCenter],RELATED(CostCenterLookup[Adjusted Cost Center]))

 

Any ideas on what is causing powerpivot to need two IF statements to return the correct value?

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Gpippin8,

     

    It should work. I guess there could be some invisible chars in the CostCenterLookup[Adjusted Cost Center]. Can you share the file?

     

    Best Regards,

    Dale

    • Gpippin8's avatar
      Gpippin8
      New Member
      8 years ago

      I can't use that file but I will find a dummy database torecreate the issue and share the file. The weird thing is that the formula works on 1 computer, but it does not work on any other computer. We have tried uninstalling and reinstalling PowerPivot and Excel and Miscrosoft Office. Non of that has worked. I'm at a loss of what to try next so I'm hoping someone else has had this issue and fixed it. 

       

      We are funning Excel 2010. 

      I wont be able to post anything until Monday to allow you to access the issue.