cancel
Showing results for
Did you mean:
Post Prodigy

## RANKX for YTD

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.
4 REPLIES 4
Super User

Just CALCULATE Function inside RANK Measure and see the result.

MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!

Post Prodigy

@VijayP As in:

RANKX YTD 2 =  RANKX (   'Customers', CALCULATE(  [Sales YTD]  )
?
No, I still get 1's.
Super User

@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.

Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Post Prodigy

@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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!