Rank a measure

[liselotte]

I have
- a dimension table 'dim_Date' with column 'Date'

- a dimension table 'dim_Customers' with columns 'Customer ID', 'Customer Name'

- a fact table 'fact_Sales' with columns 'Customer ID', 'Date', 'Type', 'Sales'.

I want to create a measure to rank Customers based of their Sum of Sales over a time period which is filtered in a slicer of 'dim_Date'['Date']. This measure will be used later in a visual graph with visual filter on 'fact_Sales'['Type']. 

This is my DAX query for the measure:

Rank = RANKX(ALLSELECTED('fact_Sales'),CALCULATE(SUM('fact_Sales'[Sales]),ALLEXCEPT('fact_Sales','fact_Sales'[Customer ID])),,DESC,Dense)

 But the function ALLACCEPT in my measure ignore filters of 'dim_Date'['Date'] and 'fact_Sales'['Type'].

Could you help me with changing the query so that it doesn't ignore filters of 'dim_Date'['Date'] and 'fact_Sales'['Type'], please? Thank you 🙂

[mark_endicott]

@Sirhawk3017 - I would suggest starting a new conversation, with more details around the issue you observed, any error you recieved and the expected result.

[mark_endicott · Accepted solution]

@liselotte - I would use the RANK function as it is easier to understand, and has better handling of blanks. Which can cause issues with ranking. The DAX code you can use is below (you just need to change the table names):

Rank = RANK(DENSE, ALLSELECTED( customer[Customer ID] ), ORDERBY(CALCULATE(SUM( sales[sales] )), DESC),LAST)

You should then make sure you use the Customer ID from the Customer table in your visual, if you include any other columns in your visual, you will need to add them into the ALLSELECTED. 

This solution is dynamic because the CALCULATE will take any filters from your other dimensions, or the type from the fact. 

See screenshots below:

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

[Sirhawk3017]

What if I wanted to rank based on a divide measure? I tried and it did not succeed.

[liselotte]

Hi @mark_endicott ,

Thank you very much for your help. It works!