SQL Server Performance Forum – Threads Archive
Calculated Member – Decimals being ignoredHello, I have a calculated member that composed of dividing two other calculated members. The calculation is incorrect and is ignoring the decimal places in both of the source measures of the equation. Example: Calculated Member 1 = 300.01
Calculated Member 2 = 10.25
Calculated Member 3 = (CM1 / CM2 ) = 30 INCORRECT!!!
Answer should be = 29.269 The MDX that I am using is as follows: Calculated Member 1 (Booked Amount YTD):
Sum(PeriodsToDate([Fiscal].[Date].[Fiscal Year]),[OH Booked Amount]) Calculated Member 2 (Units YTD)
Sum(PeriodsToDate([Fiscal].[Date].[Fiscal Year]),[OH Units]) Calculated Member 3 (Booked Amount / Unit)
Measures.[Booked Amount YTD] / Measures.[Units YTD] This seems like a fairly common and straight forward type of calculation, taking the YTD amount / YTD units to come up with YTD amount per unit. When I create a calculated member with the hardcoded values of 30.01 / 10.25 I get the correct answer of 29.269. Am I missing something with formatting? Is this a bug in Analysis Services? Any help would be appreciated. Thanks in advance. Brian
Declare @A float ,@B float, @c float
Set @A = 300.01
Set @B= 10.25
Set @c = (@a / @b )
select @c is giving me 29.269268292682927 what is your data type of Calculated Member 1 and others —————————————-
The data types for both files are float in the RDMS. I’ve tried various formatting (includidng none) within Analysis Services for each and that doesn’t seem to make a difference.
Open the cube under Cube Editor (The cube in which you have those measures and created calculated member) Click on the both the measures and check the Data Type of them.
Change them to Currency and then under data tab check the values of all three measures. Because the calculated Member takes its data type from the measures.