7 Replies Latest reply on Apr 24, 2017 8:32 AM by Steve Ellis

# Calcuating a 5% reduction from a baseline value

I am 2 weeks into Tableau and can't seem to find my answer by searching the forum--I apologize if I missed it as well as using incorrect terminology.

I have data from 2005 to current with actual counts in a column.  What I am wanting to end up with is a graph that will have bars for the Actual Count and a line showing my 'target count'.

Target count = 5% reduction from baseline year of year.

So if my first value in a table is 10000 that will become my BASELINE, year 2 would be 5% less (950), year 3 would be 5% off 950 (903), year 4 would be 5% off 903 (857), etc..

I can write a formula to take 5% off the prior year actual count but not 5% off the prior year 'target count'.  I hope this makes sense.

Attached is an excel sheet that has some data and may make more sense.

• ###### 1. Re: Calcuating a 5% reduction from a baseline value

Hi Steve,

For visualization, I have taken the xls you posted and did the dual axis chart as below. Actual count will be a bar chart and Target will be a Line chart.

But its not clear to me, if you are also looking to calculate the target on the fly.

• ###### 2. Re: Calcuating a 5% reduction from a baseline value

Thanks Karthik, sorry for not being clear.

I am needing to know how to calculate the target count 'on the fly'.  In my data set I do not have a variable target count, so I need to create that calculation.

Thanks.

• ###### 3. Re: Calcuating a 5% reduction from a baseline value

If anyone is still able to assist, that would be wonderful.  Thanks.

• ###### 4. Re: Calcuating a 5% reduction from a baseline value

Hi Steve,

If I understand you right, is this what you are looking for?

(LOOKUP(sum([Target Count]), -1)) * 0.95

• ###### 5. Re: Calcuating a 5% reduction from a baseline value

I'm so sorry, I don't think I am being very clear.

So in my data set I only have the Variables YEAR and ACTUAL COUNT.  I don't have a variable called Target Count.  I added that variable in the excel file to show what I WANT the calculation to provide me.

So what I need is some sort of calculation that will yield a 5% reduction year over year from the first YEAR in my data set.

THIS IS THE CURRENT DATA in a VIEW What I want the new view to show is BELOW.  TARGETCOUNT would have to be calcualation that looks at the first year ACTUALCOUNT and then takes off 5%, the second year would then have to look at the 'TARGETCOUNT' from the prior year and take off 5%, etc..

So sorry for not being clear and I appreciate your patience.

CURRENT VIEW

 Year ActualCount 2005 10000 2006 9500 2007 9300 2008 9400 2009 9600 2010 9300 2011 9200 2012 8700 2013 8800 2014 8600 2015 8500 2016 8200 2017 8000

NEW VIEW DESIRED

 Year ActualCount TargetCount 2005 10000 10000 2006 9500 9500 2007 9300 9025 2008 9400 8574 2009 9600 8145 2010 9300 7738 2011 9200 7351 2012 8700 6983 2013 8800 6634 2014 8600 6302 2015 8500 5987 2016 8200 5688 2017 8000 5404
• ###### 6. Re: Calcuating a 5% reduction from a baseline value

hi Steve,

So how is this for you?

I've used the self-referencing PREVIOUS VALUE and an LoD to set the initial year...

[First Year Count]

IIF([Year] = {MIN([Year])}, [Actual Count],NULL)

and then

[Target]

IF ISNULL(SUM([First Year Count])) THEN

PREVIOUS_VALUE(SUM([First Year Count])) * 0.95

ELSE SUM([First Year Count])

END

Hope that does the trick, but let me know if not

1 of 1 people found this helpful
• ###### 7. Re: Calcuating a 5% reduction from a baseline value

THANK YOU!

This worked perfectly.  Very much appreciated to everyone that helped.