cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ToddChitt
Post Prodigy
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
VijayP
Super User
Super User

@ToddChitt 

Just CALCULATE Function inside RANK Measure and see the result.




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


@VijayP As in:

RANKX YTD 2 =  RANKX (   'Customers', CALCULATE(  [Sales YTD]  )
?
No, I still get 1's.
amitchandak
Super User
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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

@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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.