How do you do a RANKX Dax calculation properly??


New Member
Dec 26, 2014
I am trying to build out a DAX RANKX formula that will rank Employee ID by Profit. Here is the formula I have today see below... However, this formula messes up when I start to layer in multiple dimensions into rows.

Rank - DO Profit by Employee:=IF(ISBLANK([DO Profit]),BLANK(),IF(HASONEVALUE('Revenue Generators'[EmployeeID]),RANKX(ALLSELECTED('Revenue Generators'[EmployeeID]),[DO Profit],,DESC,Dense)))

It works when I just have Employee ID and Profit in the pivot, however, once I start to add in the metadata/dimensions into rows the rank starts to change dynamically. I do want this to an extent, but not for every metadata layered in. Is there a way I can tell excel or add into the equation to rank employee ID by profit within the Region group, but ignore any other levels added into rows??

For example, if I have an Employee ID and Profit measure in the pivot, it works perfectly. If I start to add in "Region" it ranks Employee ID within each region AND THIS IS OKAY TOO... However, if I start to add in Title, Manager, City State, etc... the RANKX calc will rank within every possible category and I only want it to rank within Region....NOT city & State & Manager, title, etc...

Is there a way I can have employee ID ranked by Profit within the region, but that's all... so if I add in additional information elements to the pivot it will ignore these levels and only rank within Region?

How would I do this and what would I need to change in the above DAX formula?


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...