RANKX displaying duplicate rank despite different quantities

DAX DAX Calculations
1

Hey Guys,

I’m trying to present a basic “Item Rank” based on an an item’s total sales using sqlbi’s ranking DAX pattern and I’m receiving duplicate ranks despite the quantities not being ties. I’m sure it’s something basic that I’m missing here.

Item Descr Total Adj Sales Item Rank
100-A 258059.97 1
101-B 238314.27 3
102-C 158684.58 4
103-D 230244.62 4
104-E 158417.51 5
105-F 154518.81 6
106-G 154253.23 7
107-H 148204.14 9
108-I 151630.45 9
109-J 147247.45 10

Total Adj Sales = SUM(FactTable[Adj Sales])

Item Rank = 
IF (
    ISINSCOPE( ItemDimTable[Item Descr] ),
    VAR SalesAmountCurrentProduct = [Total Adj Sales]
    VAR ItemRank =
        RANKX(
            ALLSELECTED( ItemDimTable ),
            [Total Adj Sales]
        )
    VAR Result = 
        IF( 
            NOT ISBLANK(SalesAmountCurrentProduct),
            ItemRank
        )
        RETURN
            Result
)

Have you seen this behavior before? Any idea what’s going awry? Thanks in advance!
MK

2

Hello @_mk7,

Thank You for posting your query onto the Forum.

Firstly, I’m not sure why to use this long formula when one single simple formula can evaluate the ranking. Below is the measure provided for the reference -

Ranking = 
IF( HASONEVALUE( Data[Item Description] ) , 
    RANKX( ALL( Data ) , [Total Sales] , , DESC ) , 
    BLANK() )

Now, this formula simply evaluates the ranking of the Item Description. Below is the screenshot of the result provided for the reference -

Result With Simple Ranking Measure

Now, I also tried the measure that you’ve provided in the post and that also evaulates the same results. Below is the screenshot of that result as well provided for the reference -

Results With Complex Ranking Measure

So now, I don’t see any problem whatsoever in either of the formula and both of them are providing the exact same results.

I’m not sure which article of SQLBI you’re following but they’ve also showcased the simple technique as well to achieve the ranking results. Below is the link provided for the reference

Also there’re several videos available onto the Enterprise DNA YouTube channel pertaining to this topic. Below are few of the links provided for the reference as well.

I’m also attaching the working of the PBIX file for the reference as well.

Hoping you find this useful. :slightly_smiling_face:

Note: If this doesn’t solve your problem than please upload the working of the PBIX file for the reference so that members of the forum can assist you in a better and efficient manner. Without which it’ll just be a guessing work and will take the time of the members and will also increase the length of the thread.

Thanks and Warm Regards,
Harsh

Ranking - Harsh.pbix (17.4 KB)

1 Like
3

Thanks for the quick and detailed response, Harsh.

Only difference I see in measure functionality is my original “Item Rank” measure was using ALLSELECTED vs. ALL. That said, when I remove any slicer selections, I’m still seeing duplicate ranks using your proposed ranking measure, although the duplicates are different.

Item Descr Total Adj Sales Item Rank (Original) Item Rank (Harsh)
A 6940127.08 2 2
B 6752238.41 2 3
C 4337028.31 3 3
D 3559020.45 4 4
E 2646052.52 6 6
F 3107353.06 5 6
G 2587750.55 8 7
H 2206410.78 8 8
I 1824831.7 10 9
J 1727241.72 10 10

I’ll plan on replicating and uploading a .pbix for further investigation. It’s massive and will require masking.

Also, this is the article I was referencing from daxpatterns c/o the sqlbi guys.

https://www.daxpatterns.com/ranking/

Thanks,
MK

4

@_mk7

RANKX has to build a lookup table internally using the table supplied in the first argument plus the measure used for ranking and then evaluates the same measure in the filter context, now lookup table will have a lower value for each row while the measure evaluated in the filter context will have a higher value due to the granularity at which both are evaluated, since you are only using one column of that table in the visual so there is a granularity mismatch between RANKX and the Matrix.

use only single column in the formula

RANKX(
ALLSELECTED( ItemDimTable[Item Descr] ),
[Total Adj Sales]
)

4 Likes
5

Hi @_mk7, did the response provided by @Harsh and @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!