How do you do a RANKX Dax calculation properly??

[tbucki1]

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?

Thanks!!!