Forum Discussion

DeanUW's avatar
DeanUW
Icon for Helper I rankHelper 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 ...
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

DeanUW's avatar
DeanUW
Icon for Helper I rankHelper 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
Icon for Solution Sage rankSolution 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.

  • DeanUW's avatar
    DeanUW
    Icon for Helper I rankHelper I
    4 years ago

    Thanks for the response!  I have attached a snip of the table relationship, I have the Broker Totals table effectively is the fact table (that has all the rev totals summed up along with the running total measure).  The Date Table & Broker Table have relationships to the Totals table on the date & person key respectively.

    • Whitewater100's avatar
      Whitewater100
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hi:

      We can try something else. Usually we don't want many to many on the dim broker.

       

      Rank =
      VAR __table = FILTER(ALL(BrokersTable),[runningtotal] > EARLIER(BrokersTable[runningtotal]))
      VAR __tableSame = FILTER(ALL(BrokersTable),[runningTotal] = EARLIER(BrokersTable[runningTotal]) && [Broker] < EARLIER(BrokersTable[Broker))
      RETURN
      COUNTROWS(__table) + 1 + COUNTROWS(__tableSame)
       
      It might work but better to share exampe data as I'm not sure about the many to many. Otherwise I'd try my first suggestion if different than what you are using..
       
      I hope this helps!
      • DeanUW's avatar
        DeanUW
        Icon for Helper I rankHelper I
        4 years ago

        Thanks again for the response!  I attempted to change the cardinality, however Power BI desktop said that none of the other options were valid for this relationship (it's matching a primary & foriegn key , Person_Key; from our DB).

         

        Attempted to use the DAX you provided. It did not work; below is the code after adjusting some of the column names & the error PBI desktop returned:

         

        Rank2 =
        VAR __table = FILTER ( ALL ( 'Broker Totals' ), [running total] > EARLIER ('Broker Totals' [running total] ))
        VAR __tableSame = FILTER ( ALL ('Broker Totals' ), [running total] = EARLIER ('Broker Totals' [running total] ) && [Broker Name] < EARLIER ('Broker Table'[Broker Name] ))
        RETURN
        COUNTROWS ( __table ) + 1 + COUNTROWS ( __tableSame )

         

        !Error!EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

         

        Thanks again for the continued support, still kind of new to this & trying to learn!