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

    Need to subtract filtered result from fixed result

    Brian Shaw

      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
          Andrea Hage

          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
            Brian Shaw

            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