Forum Discussion
Community ChampionCalculated column for RANK by product and person
Good morning,
Can someone help me with this? I am trying to create a calculated column which creates a rank based on product by sales person. The rank itself establishes the order by date. In other words, a sales person has a number of products which they sell consecutively: for example, "James" sells product "A" from the 25 May until 1st June, and then moves onto product "F" for a few days and then another product (etc). What I am interested is in establishing the order by date a person has sold each product.
I have suceeded in establishing the total order of products sold using the RANK.Q function with the related date, but I can't seem to find the way to establish this order by person and product.
This is the original table:
|
And what I'm trying to get is the final column ("expected ranking")
Thanks!
hi PaulDBrown
For this case, you could use EARLIER Function in the formula as below:
Column 2 = RANKX(FILTER('Table','Table'[Sales Person]=EARLIER('Table'[Sales Person])),'Table'[Min date Product and sales person],,ASC,Dense)https://docs.microsoft.com/en-us/dax/earlier-function-dax
Regards,
Lin
3 Replies
Column = VAR _name = 'Table'[Sales Person] RETURN RANKX(FILTER('Table','Table'[Sales Person]=_name),'Table'[Min date Product and sales person],,ASC,Dense)please create a calculated column
PaulDBrownAnonymous
Thanks a million! worked like a charm!
v-lili6-msftCommunity Support
hi PaulDBrown
For this case, you could use EARLIER Function in the formula as below:
Column 2 = RANKX(FILTER('Table','Table'[Sales Person]=EARLIER('Table'[Sales Person])),'Table'[Min date Product and sales person],,ASC,Dense)https://docs.microsoft.com/en-us/dax/earlier-function-dax
Regards,
Lin
