I am trying to build out a DAX RANKX formula (i believe) 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/diminsions the rank starts to change dynamically. I do want this to an extent, but not for every metadata layered in.

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... to the pivot also, 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?

Something like this.... RANK ID by profit within Region (if added to pivot) and exclude anything thing else if added to the pivot?

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

Thanks!!!