Rank()

[Ranjanac]

On what basis, we can use Rank() function to Show Top 3 customer name which is having highest Revenue.

and, how to write this expression ?

Customer ID	Customer Name	Location	SalesPerson Name	SalesPerson City	Revenue	Profit
101	Elon	America	Elon	Newyork	10000	500
102	Mukesh	India	Sundar	Newyork	40000	200
103	Jeff	America	Mukesh	Mumbai	70000	300
104	Sundar	America	Narendar	Delhi	80000	700
105	Jack	China	Vijay	Hyderabad	500000	900
106	Steve	America	Amit	Kolkata	20000	600
106	Tim	America	Jayanth	Bhuvaneshwar	100000	500
107	Larry	America	Tony	London	350000	300
108	Indira	India	Peter	Dubai	150000	900

[Benoit_C · Accepted solution]

Hi @Ranjanac,

In order to show the top 3 customer names with the highest revenue, you can follow these steps:
 

- Create a measure called "total revenue for each customer" that calculates the total revenue for each customer. You can do this using the Sum() function:
  Sum(Revenue)
 

- Create a chart or table visualization in your app where you want to display the top 3 customer names.
 

- Apply a dimension limit to the visualization to show only the top 3 ranks. You can do this by going to the "Dimension" tab of the Customer Name colomn properties and setting the "Limitation" to "Fixed number" and "Top".
 

Regards,
Benoit

[simsa · Accepted solution]

In which chart are you trying to do this?

In Benoit's response above, you can put limit 3, and uncheck 'Show others' to display just the top 3.

And regarding the rank() function, I generally use it to color bars according to the measure

if(RANK(SUM(Revenue))<=3,'red','blue') (In the color by expression option)

[simsa · Accepted solution]

Alternately,

Put dimension as:  if(aggr(rank(SUM(Revenue)), [Customer Name]) <=3 , [Customer Name])

And exclude null values, Hope it helps

[Ranjanac · Accepted solution]

Yes, Thanks a lot 🙂

[Ranjanac · Accepted solution]

Thanks a lot 🙂

[sidhiq91 · Accepted solution]

@Ranjanac  You can also use below expression in your Dimension:

Aggr(if(Rank(Sum(Revenue))<=3,[Customer Name],null()),[Customer Name])

[Ranjanac · Accepted solution]

Thanks 🙂