CUBE VALUE formula recognizes two sets of identical text differently

Cube Value 1.JPG

I am using the exact same CUBEVALUE formula in both rows, which are referencing the blue text (Central costs).

The first row has 'Central costs' which is pasted from the line item retrieved from Power Pivot.
The second row has 'Central costs' which has been typed manually, but is exactly the same as what was pasted from Power Pivot above.

When I checked that these two 'Central costs' cells are equal, the formula returns TRUE, but the CUBEVALUE formula for some reason results in an #N/A. Everything else is identical.

Has anyone experienced this issue before and how do you fix it?

Thank you

Thanks. The workbook has a lot of confidential data which is why I haven't attached it. To strip out all of the confidential data would require a lot of work so I was hoping the problem could be illustrated by the picture and explanation that I posted above? Is there something that doesn't make sense?

Karen, it could be that there are hidden characters transferred from Power Pivot in C36. Assuming that the hidden characters causes the problem, you can try one of two fixes: 1) Use a "trim" formula in c36 and 2) in c37 use the formula =$c$36

Hi MaraisV, thanks for your response. It does work using '=C36', however that still doesn't solve the problem of trying to reference a new line item by typing that.

For example I have added an attachment.

The yellow cells all contain the same CUBEVALUE formula.

In the second row of each group, I have manually written each line item. In column D you can see that the two texts are exactly the same. For Contribution Margin, Invested capital, Net Working Capital - when I write these, they all result in errors. It's not just that - if you go to Ccll C8 and hit F2 and then Enter, that also results in an error (even though you haven't changed any of the spelling or format or anything).

For EBIT, EBITDA, Financing it appears to not be an issue. Do you have any idea why? Is it a coincidence that the one word line items (EBIT, EBITDA, Financing) don't have an issue but the two word line items do?

Thanks,
Karen

I have tried to simplify the formula and data to see if the results change, but the error is consistently the same. Looking at your imported data it would appear that the values in the cells are the same. So, I have a few theories what could be wrong but I would suggest that you look at the source data and see if there is a hidden character in the space between "central" and "costs". Copy "central costs" from your source data, paste it in MS Word and switch on the show/hide button.
Other than that, I can't see what is wrong. Sorry...

Hi. I faced the same issue and was searching for an answer and came across this forum. After many trials and error, I believe I have found the solution.

Instead of typing it manually in a cell(Central costs), use CUBEMEMBER function.

In my case, I was having difficulty to make the formula work for 'Sales'. I found no difference between the text I typed as 'Sales' and 'Sales' from Power Pivot.

Then instead of typing 'Sales' manually into a cell, I tried CUBEMEMBER function to bring the text 'Sales' into that cell. This did the trick. The CUBEVALUE formula in another cell was able to detect this.

I hope this helps.


In cell L11 this is the formula =CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sales]") --- Before in cell L11, I typed 'Sales' and didn't work.

In cell O11 this is the formula =CUBEVALUE("ThisWorkbookDataModel",L11,"[f_Table].[Product].[Oats]")