RankX Dense

[DeanUW]

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

[v-yalanwu-msft]

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]

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]

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]

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]

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]

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!

[Whitewater100]

Hi:

N.P. I beleive to answer the question I would need to see some data in the set up you are using. 

So much depends on the tables, relationships and the context.

I'm happy to help but would need something to work with.

Thanks..

[DeanUW]

Hello,

I would appreciate more help with this rank issue.  How would i go about sharing the pbix with you?

[Whitewater100]

Hi:

If you don't want the ranking to apply to totals or anywhere besides next to Broker[Full Name] you can use this for rank. Just a minor tweak.

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

[DeanUW]

Good Morning,

Your original DAX measures that you made on my file yesterday were the correct solution.  I did have to rebuild the entire report based on you feedback of using the dates incorrectly.  Once I rebuilt the 'Sales Totals' table to leverage the 'Date Table' more effectively your YTD & Total Sales DAX measures worked perfectly.  Thank you again for the different perspective on how to build it.  Have a great weekend!

[Whitewater100]

Great and you are welcome!

[Whitewater100 · Accepted solution]

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]

Hi,

Thank you for the adjustments you made.  When I attempted to add the Rank measure you created on to the remaining pages that list out all 450+ brokers; the rank broke again, it ranked everyone 1.  I will attempt to make the month column adjustments you recommended & see if that makes a difference.

[Whitewater100]

Hi:

Yes, the adjustments should help with the measure. I noticed your fact table was joined on "pending" date, so that date is driving the results. I also saw the YTD and Total Sales measure yield same number. Hopefully that sounds correct to you.

Good luck!

[Whitewater100]

Good morning:

You can either post here as link or if you want,my email skelleybill@gmail.com. Sorry for the delay!

[v-cazheng-msft]

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