Data Model has wrong calculation

[masplin]

Has anyone ever come across this with the 2010 add-in as quite disturbing. I have come across this a few times recently where the contents of a cell in the powerpivot window for a calculated column is just plain wrong. Mostly it is something as simple as adding 2 other calculated columns together. If I add a "+0" to the formula it recalculates correctly and then I remove that and the answer is now correct.

however this means I just can't trust any of the output. I'm really worried I may have this issue in multiple places, but only come across it by fluke.

Mike

 

[GDRIII]

We're probably going to need a sample workbook if possible. Post to Dropbox, Google Drive, etc...

 

[masplin]

Tricky as it is 600Mb. Also of course i have fixed all the ones iIve found. When I find another one I can try posting, but at this stage just wanted tofind out if anyone else had come across this and knew of a cause?

Mike

 

[GDRIII]

I have not seen any calculation issues.

Maybe some formatting anomaly...PP does it's best to figure out how to format each column when you bring data in.

Check the data type of the columns you are seeing the issue with in the PP window (Center of Home tab) to make sure they are formatted as some form of a number.

DAX is smart enough to add a column full of numbers labeled as Text and a column labeled as Whole Number.

I'm guessing this is the root of your issue.

 

[masplin]

thanks i'll dobule check, but since these are calculated columns say adding 2 number together you would have exected it to default to a number!!!

 

[PentaGalCXO]

I run Excel 2013 and find that quite often the measures displayed in the PowerPivot Window are incorrect. Typically it is after I have made changes in related formulas, the measures do not seem to recalculate. I am able to force a recalculate by adding a space to the end of formula (same effect as your +0, but my space goes away on its own). Forcing the recalc is only for my own piece of mind as the measures surfaced in Excel tables/charts are correct. I can live with it, but I do hope this gets fixed as it makes it just that much harder to convience my customers to adopt PowerPivot...

 

[masplin]

that is really disturbing if you can't rely on the calculation having completed correctly. I have not found any pattern to when these things happen as pure chance I come across a number that doesn't make sense.

 

[masplin]

Here we go complete garbage. I am adding to numbers together that are in calculated columns
[Retail Price] = 82.50
[Service discount]=-16.75
Actual Price Paid = =round([Retail Price]+[Service Discount],0) ---answer 439,139,68....... !!!!!!!!

all 3 columns formatted as Currency. Tried turning ot manual calc and back to automatic and no difference. Have saved the workbook in a dropbox folder. Go to powerpivot window tab "MatchInput" far right column

Tried changing round to 1 says has wrong data type or is too large or small???? Actually the erro is elsewhre. Go to "transaction" tab and filter first column for invoice "

06C31466". in column [Act Discount] for STCODE DISPCC1 it says NaN. I added a +0 to the true part of the IF statement and everything now calculates correctly (I think but who knows). This field is also formatted as Currency. Any clues why this would happen as really worrying as we are basing marketing decisions off these results? I'm notsure how i share a 250Mb file so you can see the issue? Mike

<tbody>
</tbody>

 

[scottsen]

I have never seen this, and indeed... it sounds rather worrying.

Sounds like you are using dropbox, you could easily share a link that way (just right click the file and click "share dropbox link", and it will give you a URL to paste here or in a direct msg).

 

[masplin]

here you go

https://www.dropbox.com/s/1tn1j0u877fv22k/INO_Data_Sep14error.xlsx?dl=0

Thanks Mike