7 Replies Latest reply on Jul 13, 2018 12:28 PM by Deepak Rai

# Trying to create a reference line with a manual calculation

Hey all,

Here is the scenario:

I have completion percentages for a number of different measures and I have a 'goal' of completion percentages.

Example: Measure 1 had a completion rate of 50% and our goal was 40%.

I am pulling in all of my raw data from a Redshift server and that source data doesn't contain the goals, so what I'm trying to do is manually add the goals in with basic calculations.

The calculated fields are essentially just ( 50 / 100) so I can get the goals.

The problem is when I go to add the goal as a reference line Tableau is not calculating it out even close to accurately.

I have one field set to 60 / 100 which gets me the decimal I need to get to 60%, but when I add it in as a reference line Tableau thinks it's 13440%. Which I don't even know how that's possible.

This data contains sensitive client info, so I can't share a workbook, unfortunately.

• ###### 1. Re: Trying to create a reference line with a manual calculation

Looks Like You are Summing Up all The Data, So Use {AVG(60/100)} to keep value Constant, Drag to Detail and Create Reference line.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 2. Re: Trying to create a reference line with a manual calculation

Thank you! That worked perfectly.

Do you mind if I ask why that worked? Do the curly brackets have something to do with it?

Just want to understand all the moving parts.

• ###### 3. Re: Trying to create a reference line with a manual calculation

It Worked Because Curly Bracket are Notation for LOD  and here in this case we are Averaging across entire Data set Irrespective of Number of lines, So the value would always remain Constant.

1 of 1 people found this helpful
• ###### 4. Re: Trying to create a reference line with a manual calculation

Ahh ok, thank you!

This is a separate question, so I can create another question if need be, but:

Say I wanted to add multiple manual goals as above but each set to target a different name

Such as:

IF [Name] = 'Joe'

THEN 60 / 100

ELSEIF [Name] = 'Smith'

THEN  15 / 100

ELSE NULL

END

Is there a way to do that?

• ###### 5. Re: Trying to create a reference line with a manual calculation

{FIXED Name: AVG(IF [Name] = 'Joe'

THEN 60 / 100

ELSEIF [Name] = 'Smith'

THEN  15 / 100

ELSE NULL

END)}

If you are using any Filters add Them To CONTEXT in this case As I used FIXED. Fixed is evaluated before any other calcs. So if you Don't want to add Filters to CONTEXT, Change word  FIXED to INCLUDE

1 of 1 people found this helpful
• ###### 6. Re: Trying to create a reference line with a manual calculation

Awesome! That, too, works perfectly.

Thank you again!

• ###### 7. Re: Trying to create a reference line with a manual calculation