2 Replies Latest reply on Nov 22, 2016 8:29 PM by Brian Shaw

# Need to subtract filtered result from fixed result

Hello Tableau Community,

I'm fairly new to Tableau and this is my first question on the Tableau Community discussion forum.  I wish I knew the terminology better as this question may have been asked before.  Anyway, in the attached file I want to subtract the result when a specific year is selected in the filter from the value when the filter is set to 'all'.

For example, Ada County is 96.2% when the filter is set to 'all' but changes to 93.2% when the filter is changed to '2004'. The result I want is 93.2% - 96.2% = 3.0%.  I tried an LoD calculation to fix the 'Birth Year', but result is always 100% which is obviously wrong.

Thanks for any suggestions.

Brian

• ###### 1. Re: Need to subtract filtered result from fixed result

Hi Brian,

I think I may have fixed the problem. I edited Calculation1 slightly:

AVG({FIXED  [Birth Month Name], [County]: AVG([Num Of Births])})

And then I created an additional calculated field called PP Diff (percentage point difference) to find the difference between the chosen year's average and the fixed average:

[Calculation1] / TOTAL([Calculation1])-AVG([Num Of Births]) / TOTAL(AVG([Num Of Births]))

Take a look at the attachment and see if this is what you were aiming for. Hope this helps!

- Andrea

P.S. I recently had to do this for my work and found it was helpful to add a + sign to the positive changes. I also changed the notation to "pp" to indicate that it wasn't a percent change, but rather a change in percentage points. I did this by slightly editing the PP Diff calculation and also custom formatting the number, which I included in the attachment in case you are interested!

• ###### 2. Re: Need to subtract filtered result from fixed result

Hi Andrea,

This works really well!  I don't exactly understand how it works, but it's exactly what I was trying to do.  I have much to learn about Tableau.

Thanks so much for your help.

Brian