THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
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] ) )


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.


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:


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: , , ,



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?



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?



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

Ernesto Herrera said:

Hi Marco, I'm trying to run one of the samples from your book using rankx:



'Internet Sales',

Product[Product Name],

"Sales", 'Internet Sales'[Internet Total Sales],

"Rank", RANKX( ALL( Product[Product Name] ), 'Internet Sales'[Internet Total Sales] )


Geography[Country Region Name] = "United States"



but in the results the ranks column is always 1 for every record, any clue?


June 11, 2015 1:57 PM

Marco Russo (SQLBI) said:

Use ADDCOLUMNS instead of grouping with summarize, and don't use table names before measure names:




       SUMMARIZE (

           'Internet Sales',

           Product[Product Name]


       "Sales", [Internet Total Sales],

       "Rank", RANKX (

           ALL ( Product[Product Name] ),

           [Internet Total Sales]



   Geography[Country Region Name] = "United States"



July 3, 2015 6:45 PM

Kenneth Borrowdale said:

Well, who would have known that ADDCOLUMNS was required to make RANKX work?  After many hours, this is the post that saved me!

November 30, 2015 7:00 PM

Marco Russo (SQLBI) said:

Thanks for the feedback, Kenneth! :)

December 30, 2015 2:50 PM

Sohail said:

Hi Marco.

I have an interesting scenario were I have to Rank Team members based on Team.

Below is just an example. Please please please help Marco. Thank you!

Team Name,Agents,Score,Rank

Team 1,Agent 8,96,1

Team 1,Agent 6,40,2

Team 1,Agent 5,15,3

Team 1,Agent 18,12,4

Team 2,Agent 6,78,1

Team 2,Agent 7,78,1

Team 2,Agent 4,69,2

Team 2,Agent 18,17,3

Team 3,Agent 13,100,1

Team 3,Agent 20,62,3

Team 3,Agent 3,78,2

Team 4,Agent 17,119,1

Team 4,Agent 15,79,2

Team 4,Agent 2,52,3

Team 4,Agent 16,46,4

Team 5,Agent 7,90,1

Team 5,Agent 6,79,2

Team 5,Agent 1,77,3

Team 5,Agent 17,55,4

Team 5,Agent 14,43,5

Team 5,Agent 12,10,6

July 22, 2017 2:22 AM

Marco Russo (SQLBI) said:

You should use something like this in a calc column of a table named Table

CALCULATE ( [Ranking measure], ALLEXCEPT ( Table, Table[Team Name] ) )

August 4, 2017 2:15 AM
New Comments to this post are disabled

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



Privacy Statement