Forum Discussion

DeanUW's avatar
DeanUW
Helper I
4 years ago
Solved

RankX Dense

I am trying to rank sales people based on YTD revenue total. When I rank the brokers based on the broker name column the rank repeats/starts over multiple times randomly.  If I rank everything based on the entire table the rank is a little more consistent however there are ties in the order despite using the Dense tie breaker.

 

Rank = RANKX(ALL('Broker Totals'[Broker Name]), [running total],,,Dense) - causes the ranking to be inconsistent, multiple people be ranked #1 regardless of the sales volume the rank starts over multiple time within the first 25 sales reps. see snip
 
Rank = RANKX(ALL('Broker Totals'), [running total],,,Dense) - gets me closer to my desired result however despite using the dense commmand in the DAX it doubles up the rank order despite their revenue totals not actually being tied. see snip
rank repeating itself randomlydense not breaking ties
DAX Commands and Tips
RANKX issue
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    Hi 😧

    I made a few changes. Brought brokers full name into your brokers table. Changed Date Table to mark as Date Table. Put new measures:

    Total Sales

    YTD Sales

    Rank Brokers

     

    I think instead of using implicit measures your better off using a measure. E.G. Total Revenue is implict. Total Sales is the measure to use. Also bring brokers name in your tables from brokers table. I don't think you need to show month sales like present. Use Month field from Dte table for all visuals. I hope this helps! I will send link in pers email.

    Rank Brokers(YTD Sales ) = RANKX(ALL('Broker Table'[Full Name]), [YTD Sales],,DESC)

    Bill

16 Replies

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    4 years ago

    Hi DeanUW,

     

    For it's something related to how to write a dax, transfer it to the right forum DAX Commands and Tips to get help from community folks.

     

    Best Regards,

    Community Support Team _ Caiyun

     

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, DeanUW ;

    Sorry, I can't judge the reason just according to your formula, can you share a simple example similar to your table, and want to output the result to show?

    Looking forward to your reply.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • DeanUW's avatar
      DeanUW
      Helper I
      4 years ago

      Thank you for responding.  Not sure I can share the data set due to confidentiality. essentially I'm just trying to rank the sales brokers based on YTD revenue total.  I included an additional snip of the revenue column.  There are a couple ties due to the brokers working as a team on all of their deals so I would want the dense command to keep the numeric order.  I used the running total DAX measure to generate the YTD rev totals, then using that measure to determine the rank as seen in the DAX expression included above.

       

      I appreciate any other assistance you can provide.

      • Whitewater100's avatar
        Whitewater100
        Solution Sage
        4 years ago

        Hi:

        Are all your figures in one table? Normally you will have a separate and unique dimension table for Brokers with relaitionship with your fact table with sales in there(and Broker_ID) to tie back to your broker table. Same thing with a Date Table, marked as Date Table with relationship to Fact Table, most liely on order date field.

         

         

         

        Rank Brokers = RANKX(ALL(BrokersTable[BrokersName]), [runningtotal], , DENSE)

        This works becasue the model is organized to work across tables due to the relationships formed.

        I will paste a typical model with one sales fact table "Online Sales" above. All Dim Tables filter downhill to the Fact Table.