8 Replies Latest reply on Jul 18, 2018 12:41 PM by pranavi billa

# Calculating Percentage

Hi,

I am trying to create a  report for my company which can help me in getting a score for each client who works for us. To achieve this I need to get a percentage of each client (Number of Issues)/(Total number of projects)*100

where the Total number of projects is different for each client - Orange: 35, Yellow:141, Green:20, Red:64  (Example: Percentage of Orange: (10/35)*100=28%). Later I want to address that particular client to be good bad or average depending on the percentage level they get.

(0-10%) a client should be in GOOD and colour coded as GREEN.

(10%-20%) a client should be in Average and colour coded as YELLOW.

(20% above) a client should be in BAD and colour coded as RED.

I want the percentage and the status (GOOD, BAD or AVERAGE) in two different columns

 Client Issues Orange 10 Yellow 7 Green 5 Red 2
• ###### 1. Re: Calculating Percentage

[Percentage] (new calculated field)

sum([number of issues])/sum([total number of projects])

Format this as a percentage

[Status] (new calculated field)

IF [percentage] <= 0.1 THEN 'GOOD'

ELSEIF [percentage] <= 0.2 THEN 'AVERAGE'

Put [Status] onto colors in the marks card.

• ###### 2. Re: Calculating Percentage

How can I modify this from sum([number of issues])/sum([total number of projects]) TO

sum([number of issues])/35)  if the Team name is orange

sum([number of issues])/141) if the Team name is yellow

Do I have to use IF condition?

• ###### 3. Re: Calculating Percentage

Where are you getting the denominator from ?

is it hard-coded, or in a different measure ?

• ###### 4. Re: Calculating Percentage

it is hardcoded

• ###### 5. Re: Calculating Percentage

Then you will need an IF as the denominator to provide the proper value.

Something like:

Sum([Issues])

/

(if [Client] = 'Orange' then  35 elseif [Client] =  'Yellow' then 141 elseif [Client] =  'Green' then 20 elseif [Client] =  'Red' then 64 end)

I haven't tested this in Tableau though, so there might be some slight syntax errors

• ###### 6. Re: Calculating Percentage

I tried the above syntax and am getting an error as cannot mix aggregate and non-aggregate arguments with this function.

Cursor points on the ' / ' symbol

• ###### 7. Re: Calculating Percentage

OK, try this :

Sum([Issues])

/

SUM(if [Client] = 'Orange' then  35 elseif [Client] =  'Yellow' then 141 elseif [Client] =  'Green' then 20 elseif [Client] =  'Red' then 64 end)

• ###### 8. Re: Calculating Percentage

worked thanks