RANKX Help!

[CourtneyB]

Hi Everyone,

I am hoping someone can help me becausebecause this RANKX is really giving me a run for my money. I am fairly new to Power Bi and have been teaching myself as I go and I have hit a wall with this one. I need to rank different companies average turn times (i.e. how long it took them to deliver the product from start to finish) from lowest to highest time by state. Any help is much appreciated. 

[CourtneyB]

Example of what I am looking to accomplish except a new column would be entered with the rankings

[mattbrice]

RANKX ( ALL (Table[Company] ), [Avg Tat BD] )

[CourtneyB]

@mattbrice it is not allowing me to enter the [avg TAT BD]. In the matrix, i have the TAT BD to average and not sum if that makes a difference?

[DAX0110]

Hi @CourtneyB, can you show us your latest version of RANKX formula ? 

[CourtneyB]

@DAX0110 I didnt have anything established as i kept getting errors but with @mattbrice's formula and adding Average() it just gave me all Rank 1s. I dont know if it makes a difference but each row in the data gives the order level Turn Time, so when i pull that field into the matrix i have it average instead of sum.

Rank = RANKX(ALL('2821_410_700_150'[CompanyName]),AVERAGE('2821_410_700_150'[TT100150_BD]))

[mattbrice]

I assumed that you only had 'Table[State]' and 'Table[CompanyName]' on the rows and 'Order Count' and 'Avg TAT BD' were already measures.   So the Rank measure as written should work.  I don't understand why you were getting an error. What specifically was the error you got, and what exactly was the measure you wrote?

[CourtneyB]

@mattbrice I have added the measure to do Avg TAT BD instead of formating it to be average in the matrix and now it let me use it in the RANKX formula however it is not populating the ranks correctly

[Ashish_Mathur]

Hi @CourtneyB,

Share the link from where i can download your file.

[CourtneyB]

@Ashish_Mathur I'm sorry, I do not know how to create a link for you to download the file. I have played around more with the data and got the formula to mostly work except there isnt a rank 1 populating. Here are some screenshot detail that hopefully will help

Current RANKX Formula

Visualization DetailsSnapshot of dataMeasure for Avg TAT BD being used

[Zubair_Muhammad]

HI @CourtneyB

Give this a shot

Rank =
RANKX (
    ALL ( '2821_410_700_150'[State], '2821_410_700_150'[CompanyName] ),
    AVERAGE ( '2821_410_700_150'[TT100150_BD] )
)

[CourtneyB · Accepted solution]

Hi All, 

So, my boss was able to figure it out. the correct formula to use for my case is the following:

Rank = RANKX(ALLSELECTED('2821_410_700_150'[CompanyName]), CALCULATE(AVERAGE('2821_410_700_150'[TT100150_CD])),,ASC,Dense)-1

Thank you for all your imput and assistance.

[mattbrice]

What is ‘Avg TAT BD’ measure you wrote?   Are there other table/page/report filters being applied to matrix?