5 Replies Latest reply on Jun 11, 2013 5:02 PM by nolan.gaskill

    Mean and Standard Deviation in Histogram

    nolan.gaskill

      While I am fairly certain that this must have been asked and answered I am striking out with my search terms.

       

      What I would like to do is add a vertical reference line for the mean and vertical shaded region for +1/-1 standard deviations within a histogram. Further I would like those values to dynamically update as you interact with filters (in this case it is only the date).

       

      When I pull the Performance (bin) the menu when I right-click the axis does not have an option for reference lines.

      http://public.tableausoftware.com/static/images/Pe/PerformancebyMarketandProduct/PerformancebyMarketandProduct/1.png

        • 1. Re: Mean and Standard Deviation in Histogram
          nolan.gaskill

          After doing more searching I understand that the reason I am not finding an option to add reference lines is due to the fact that creating a histogram creates the buckets in discrete units.

           

          Is there a generalized suggestion on creating buckets using an integer function that would still allow the user to select the bucket size using a parameter?

          • 2. Re: Mean and Standard Deviation in Histogram
            Jim Wahl

            Hi Nolan,

             

            Try

             

            INT([Value]/[Bin Size])*[Bin Size]-IIF([Value]<0,[Bin Size],0) // Last IIF part required for negative values

             

            Bin Size can be a parameter.

             

            Once you add this table calc, it'll default to a measure, but you can move / convert it to dimension and make it continuous. Then use bar chart and adjust the bar sizes accordingly.

             

            Another advantage of this approach is that you can use a line chart and make it a "frequency polygon", which can work better for displaying the shape of the distribution (and is somewhat less effective than bars if your goal is to compare the size of each bin).

             

            Jim

            • 3. Re: Mean and Standard Deviation in Histogram
              nolan.gaskill

              Thanks so much.  That works brilliantly.  The issue I am having now is that when I try to drop in my reference lines to show average and standard deviation, it appears those values are not correct.  I assume that what I am seeing is an average & stdev of the binned values rather than an a true average of the underlying data.  i.e. instead of (31 + 42 + 53)/3 = 41.6, I see (30 + 40 + 50)/3 = 40.  Is there a solution I'm not finding where I could use a dual fixed axis to represent the underlying statistics?

              • 4. Re: Mean and Standard Deviation in Histogram
                Jim Wahl

                Glad to help..

                 

                To get the unbinned mean and SD, just drag the [Value] field to the detail shelf / button. Now you can select that field in the reference line dialog box. For SD, you'll need to create calculated fields (mean + 1SD, ...).

                 

                Jim

                1 of 1 people found this helpful
                • 5. Re: Mean and Standard Deviation in Histogram
                  nolan.gaskill

                  Thanks, got me on the right track.  I had to end up using a table calculation but same basic principle.  You're a star.