THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Use of RANKX with decimal numbers in DAX #powerpivot #ssas #tabular

Using decimal numbers in Power Pivot and Tabular might produce small rounding differences in certain calculations. This is nothing new when you work with floating point, as many programmer knows. The implementation of RANKX might suffer of a behavior producing wrong results when the measures used for the ranking returns a decimal value.

For example, consider the following model, where there are three names (A, B, C), each one with a value resulting from the sum of rows in the fact table and a Pos measure, calculated using the following measure:

Pos :=
IF (
    HASONEVALUE ( Sample[Name] ),
    RANKX (
        ALL ( Sample[Name] ),
        CALCULATE ( SUM ( Sample[Value] ) )
    )
)

clip_image001

In this case, everything works fine and the Pos has values from 1 to 3. However, when you select only one name, you might see a wrong number. In the following example, the Pos value is higher than the number of available names.

clip_image002

It is not easy to find a reproducible case, usually the rounding error results from complex calculations. The purpose of the previous example is to describe the symptoms that you might experience.

Under the cover, the RANKX calculate the value of the measure for each element of the list of names, and then it searches in that table the result of the expression for the current filter context. If there is any rounding error in this operation… the match does not happen (or it might happen with the wrong index, even if this is harder) and you see the wrong Pos number as a result.

Hopefully, a fix to this behavior will be released sooner or later. In the meantime, there are two possible workarounds:

  1. Cast the expression to currency using the CURRENCY function, so that the values compared are of currency data type, which is not subject to the described issue
  2. Store the original value in a column of Currency data type, so that the result is still a currency and the match works well

By using either one of the workarounds, you will see the correct result:

clip_image003

The first approach (cast the result) might have a minimal impact in query performance. I would prefer, whenever possible, storing the values in a Currency column, so that any measure will not suffer of this issue.

In any case, be careful about the data type of the expressions using in a RANKX function.

Published Wednesday, July 16, 2014 8:22 PM by Marco Russo (SQLBI)
Filed under: , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Irma said:

Hi Marco,

thank you for the post - very useful. We have noticed a similar behaviour with the SUM function and decimal measures. When we slice the cube by a dimension and a decimal measure (which is just SUM([decimal_column]), it shows one value under member value X and the sum of the measure values does not add up to the total sum displayed in Excel. However, filtering on member value X produces a different value in the measure. When we changed the decimal_column to be a currency data type as you suggested for the RANKX function, we don't see this inconsistent behaviour, but the issue is that we need to have a higher accuracy than decimal places. The inconsistent behaviour for the decimal values in Tabular is worrying though. Do you maybe know why this is happening?

Thanks,

Irma

September 16, 2014 5:25 AM
 

Marco Russo (SQLBI) said:

Irma, the problem is related to the implementation of RANKX: it builds a list of values and, when it looks for a value in order to get the index (the position), if the rounding error fails the match, the result is completely wrong.

The current workaround is to at least do the rounding to a money for the expression that applies the RANK (without changing the data type for the original measure that you want to display). If the decimals removed by the rounding are relevant for the ranking, you might consider to multiply the original value before doing the cast.

September 16, 2014 7:08 AM
 

Irma said:

Hi Marco, we are not using the RANKX function but the SUM function and have noticed that the SUM function does not give consistent answers when used on a decimal value e.g. slicing by a dimension attribute and filtering on one of its members does not give the same results for the same member.

In other words, the two MDX queries below return different results for member "B".

SELECT [dimTest].[Name].Members ON rows ,

([Measures].[Decimal_sum]) on columns

FROM [Model]

SELECT [dimTest].[Name].&[B] ON rows ,

([Measures].[Decimal_sum]) on columns

FROM [Model]

Could this be a bug with the SUM function and decimal values similar to the bug with the RANKX function that you have described?

Thanks,

Irma

September 16, 2014 9:49 AM
 

Marco Russo (SQLBI) said:

Irma - this seems to be another issue.

Do you see a difference in decimals or a completely different (or missing) number?

September 16, 2014 10:54 AM
 

Irma said:

There is a difference in whole numbers which can be quite high. I am assuming that it is a rounding error that accumulates.

September 16, 2014 11:51 AM
 

Marco Russo (SQLBI) said:

Irma, a rounding error that accumulates is very strange. Are you able to repro this in Adventure Works? If not, I would suggest you opening an incident support case with Microsoft, submitting the model with the errors and helping them to find any bug. Keep me updated, thanks!

September 16, 2014 12:01 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement