2 Replies Latest reply on Dec 3, 2018 4:22 AM by Zhouyi Zhang

# Ranking of SalesForce people

Dear Friends,

I am trying to make a ranking of SaleForce people that consider 7 KPI. For each KPI I created a formula that gives points for % of realization and it looks like that:

FLOAT(IF   [KPI1]>=1.1 THEN "6"

ELSEIF  [KPI1]<1.1 and [KPI1] >=1 THEN "5.5"

ELSEIF  [KPI1]<1 and [KPI1] >=0.95 THEN "5"

ELSEIF  [KPI1]<0.95 and [KPI1] >=0.9 THEN "4.5"

ELSEIF  [KPI1]<0.9 and [KPI1] >=0.85 THEN "4"

ELSEIF  [KPI1]<0.85 and [KPI1] >=0.8 THEN "3.5"

ELSEIF  [KPI1]<0.8 and [KPI1] >=0.75 THEN "3"

ELSEIF  [KPI1]<0.75 and [KPI1] >=0.7 THEN "2.5"

ELSEIF  [KPI1]<0.7 and [KPI1] >=0.65 THEN "2"

ELSEIF  [KPI1]<0.65 and [KPI1] >=0.6 THEN "1.5"

ELSEIF  [KPI1]<0.6 and [KPI1] >=0.55 THEN "1"

ELSEIF  [KPI1]<0.55 and [KPI1] >=0.5 THEN "0.5"

else "0" END)

and for other KPI formula is the same just the KPI changes...

The formula returns me good results when I pick ONE MONTH but what I want to do is to SUM points from each KPI per MONTH and then make an AVG. I can't do that formula because it says that the data is already aggregated and I cannot aggregate if further. I tried to do it like that:

([KPI1}+[KPI2}.......+[KPI7})/7 and then it works but when I select two months on filter then Tableau counts it bad. It does it like that - first of all it counts the realization of two months together and then it gives points from formula. What should I do to make Tableau first give points per month and then in the next step sum these points per month and make avg of them.

MiĆosz.

• ###### 1. Re: Ranking of SalesForce people

Hi Milosz,

In your calculation you are performing a row level comparison whereas what you are after is the aggregate level. You can wrap the aggregation around your measures, for example:  SUM([KPI]). Additionally you don't have to return a string (denoted by quotations) for each label and wrap that around with a float function but directly return the float. I've done a small example with sample superstore to help illustrate:

Let me know if this helps or you need further guidance.

Cheers,

 Sasha HannaCertified Tableau & Alteryx Trainer, The Information Lab  www.theinformationlab.co.uk
• ###### 2. Re: Ranking of SalesForce people

Hi, Milosz

without seeing your workbook, it is hard to provide a accurate solution, it seems your KPI is already an aggregation calculation, so probably you could try