SSAS – Getting a negative value for measure that has been used in a SUM
When you are browsing data from a cube, you see the grand total or a value in a column it has a negative value. This is while you know that it does not have any negative values so this must be wrong. The reason for the value being negative is due to the fact that data type in your cube is too small to show the correct value. To correct this you will need to change your data type with the details below
In this example we are working using the Adventure Works DW 2008R2, and using the Fact Internet Sales Measure Group.
1. This is due to the data type that is used within the cube is too small for your value.
2. So what happens is that it is an overflow and displays this with a negative sign at the start “-“
3. To get around this you need to do the following:
1. Open your cube where you are getting this error
2. Click on the Cube Structure Tab
3. Then right click on your measure that is having the issue and click on Properties
1. In our example this was Sales Amount
2. Now in the Properties click on the plus sign next to Source
3. Where it says DataType change this to a bigger value
a. Example: Our data type was INT which was not big enough for the sum of our values, so once this was changed to a BigInt it went away.
4. Once that is done re-process your cube.