RANKX for YTD

[ToddChitt]

Hello all:

This seems it should be fairly straight forward bu still eludes me.

I am trying to get a RANKX of Customers based on YTD Sales, such that the Ranking will respect the Date slicer on the page.

The model is quite simple: 3 tables, appropriately related

* Sales (fact)

* Customer (dim)

* Date (dim)

I have a [Sales Sum] and [Sales YTD] measures defined in the fact table

* Sales Sum = sum('Sales'[ExtSales])

* Sales YTD = TOTALYTD([Sales Sum],'Dates Table'[Date])

I think this needs to be a MEASURE, not a calculated column.

I have tried this:

RANKX YTD =

     RANKX (

          ALL('Customers'[customer_name]),

                 CALCULATE(

                 [Sales YTD]

              )

         )

But a simple table visual with Customer Nam shows 1 for every [RANKX YTD] (There is a Date slicer on the page to give the calculations some date context.

Any help would be appreciated. Thanks in advance.

[VijayP]

@ToddChitt 

Just CALCULATE Function inside RANK Measure and see the result.

[ToddChitt]

@VijayP As in:

RANKX YTD 2 =  RANKX (   'Customers', CALCULATE(  [Sales YTD]  )  )

?

No, I still get 1's.

[amitchandak]

@ToddChitt , are you using anything other than 'Customers'[customer_name] in the visual that is not summarized. If so 'Customers'[customer_name] will rank inside that.

[ToddChitt]

@amitchandak No. My table visual contains only Customer_Name, but the data table is related to Sales on Customer_ID. 

One issue I am fighting with the data owner is that Customer_Name is not unique in the Customer table, but ID is. Not sure if that matters.

Thanks