cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gpippin8 Frequent Visitor
Frequent Visitor

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 2
Community Support Team
Community Support Team

Re: PowerPivot not returning the the correct value?

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Gpippin8 Frequent Visitor
Frequent Visitor

Re: PowerPivot not returning the the correct value?

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.