Rank Based on Measure

[JimSales81]

I'm putting together a stack rank for my sales dept.  

I have 10 measures, things like:

1.) Total Sales

2.) New Customers

3.) % to Quota

4.) % of Total Sales is our Newest Product

Etc.

I now want to rank each measure 1st to last and total the Rank for each Measure.  The person with the lowest combined score will be ranked first.  How can i do it? Can i make it dynamic?  I.e. use the same Rank Measure to rank the Area Directors, the Regional Directors, and the Territory Sales People ?  

Thank you!

[mark_endicott · Accepted solution]

@JimSales81 

You can use RANK to do this, and it is fully dynamic an example of this would be below:

RANK(DENSE, ALLSELECTED( **** ) , ORDERBY( **** ,ASC), LAST, PARTITIONBY( **** ))

Inside the ALLSELECTED, you must put any columns you would like to Rank by, if you are ranking people then this column must go here - and any other column you intend to use in your visual. 

Inside the ORDERBY is the measure you wish to rank. 

PARTITIONBY is optional, and gives you the freedom to reset the rank at certain points, for example you wanted a rank within each Area Director. If you do not need to break your rank at certain parts, simply remove:

, PARTITIONBY( **** )

If this works for you, please mark it as the solution.