RANKX - Strange behavior

Hi Dars

I have to agree that RANKX can be confusing at first! :)

This page on PowerPivotPro has a few examples.

I'm assuming you are trying to rank each ExampleTable[id] by [Number of rows]?

To cut to the chase, a formula that should achieve what you're trying to do is:

Rank =
IF (
    HASONEVALUE ( ExampleData[id] ),
    RANKX ( ALL ( ExampleData[id] ), [Number of rows] )
)

and add this to a visual/table with ExampleData[id] on the rows/axis, something like this:

 Just to explain RANKX in general:

1. The 1st argument is a table over which the ranking takes place, so the table has to be at the granularity that makes sense for your ranking.
   I think ALL ( ExampleData[id] ) makes sense in your case (as it contains a row per value of id, rather than ALL ( ExampleData ).
2. The 2nd argument is an expression that is evaluated in the row context of every row of the table in the first argument. 
3. Lastly, the expression in the 2nd argument is evaluated in the current filter context, and is then "ranked" among the values of the expression determined in step 2. (If you want, you can provide a different expression to be ranked as a 3rd argument).

The HASONEVALUE check is just there to ensure the calculation only happens in the context of a single value of ExampleData[id].

This formula says:

1. Check that there is exactly one value of ExampleData[id] in the filter context
2. If so, construct a single-column table of all values of ExampleData[id], ignoring any filters.
3. For each row of this table (i.e. for each value of ExampleData[id]) calculate [Number of rows]
4. Separately, calculate [Number of rows] in the current filter context.
5. Compare the Step 4 value of [Number of rows]  against the Step 3 list of values of [Number of rows] , and determine its rank.
6. This rank is then returned.

Your previous formula was calculating the rank by iterating over all rows of ExampleData. The value of [Number of rows] is exactly 1 when evaluated in the context of each individual row. However, for each id there is more than 1 row, so the rank of [Number of rows] was top (i.e = 1) for any value of id.

Also, it was just by chance that [Total Value] gave you sensible ranks in your original formula.

Post back if that wasn't what you were looking for.

Cheers,

Owen