9 Replies Latest reply on Dec 27, 2010 8:03 PM by Richard Leeke

    Calc Median and Average Age

    . marjenmiller

      I'm trying to calculate the median age vs. average age of a list of products based on sales within a linear graph that shows the sales by Product. I want to show a reference line for Median and one for Average within the graph. I know how to calculate the average/median in Excel, but want to implement it into Tableau. If I have the following data columns of data: Product, Age of Product, Number of Sales with these samples (attached):

      Product     Age     NumberSales

      Jelly-A      1      4,000

      Jelly-B      2      6,000

      Jelly-C      3      8,000

      Jelly-D      4      5,000

      Jelly-E      5      3,800

      Jelly-F      6      2,000

      Jelly-G      7      4,000

      Jelly-H      8      1,000

      Jelly-I      9      1,000

      Jelly-J     10      1,200

       

      How can I do it? I have seen the example of calculating a Median and Average values when adding a reference line, but all I get is a median of "5", which is the midpoint of the Age value, not based on the calculation of the number of sales within the age. The actual median age value for this example should be "3.00" and the average age value should be "4.11"

        • 1. Re: Calc Median and Average Age
          James Baker

          I'm sorry, a weighted average is not offered as a value option for reference lines.  I've filed this for future development consideration.

          • 2. Re: Calc Median and Average Age
            . marjenmiller

            Bummer!!  Thanks for the reply and I hope to see it in a future release.

            • 3. Re: Calc Median and Average Age
              guest contributor

              Has any progress been made to date on the inclusion of a weighted average? I gather, also, that the percentile reference lines are not weighted either?

              • 4. Re: Calc Median and Average Age
                James Baker

                This feature has passed through consideration and been placed on our future to-do list, but has not yet been implemented.  Thank you for your patience; we are always working furiously to improve our products and make them more useful - even if it doesn't feel like it because each user only touches a fraction of any new functionality.

                • 5. Re: Calc Median and Average Age
                  . CTurner08

                  One more vote for moving this up on the priority list.  As a relatively new Tableau user, I was hoping it would help with analysis of some survey data with about 5000 observations, each of which has an associated weighting factor to accurately represent the entire population distribution. My quartile and percentile calculations can be misleading if those weights aren't included.

                  • 6. Re: Calc Median and Average Age
                    James Baker

                    Our notes on this feature item now say "v5.1 reference lines support these use cases".  Version 5.1 was released in February of this year (2010).

                     

                    I'm not sure on the details at the moment, but thought I'd share that good news.

                    • 7. Re: Calc Median and Average Age
                      . CTurner08

                      On a quick look at adding reference lines, I don't see an obvious option to automatically do a weighted MEDIAN (or other quantile).  I've attached a tiny spreadsheet that might clarify what I'm looking for.  Thanks!

                      • 8. Re: Calc Median and Average Age
                        guest contributor

                        I have a project that has several set of data.

                         

                        Column 1: States

                        Column 2: Sales.

                        Column 3: Stores

                         

                        The results needs to show in two way in the dashboard:

                        1st respot has to Show an average of all values based on selected store break down by State. 

                        Second card on the same dashboard needs to show the average of other stores and state excluding the store that was selected.

                        So the same filter has to show me average based on selected store and other set of data has to show me average EXCLUDING the selected store.  Global filtering is INCLUDING both.  Is there a way to include the filter choice for one report and EXCLUDe for the other ?  INstead of having two same Storename filters in Dashboard ?

                         

                        Thanks

                        • 9. Re: Calc Median and Average Age
                          Richard Leeke

                          You could use a parameter and then use two filter expressions, one including the selected store and one excluding it.

                           

                          You can define the valid values for the filter from a field in your datasource - the only trouble is that this is not dynamic, so won't automatically update as new stores are added.

                           

                          The other way is to use filter actions, like so:

                           

                          1) Create 3 sheets: one will be your store selector, one will show averages for that store and one will show averages excluding that store.

                          2) Add all 3 sheets to a dashboard.

                          3) Create a filter action, activated on select with a source of your selection sheet and a target of the other two sheets on your dashboard.

                          4) Select one of your stores.  This will initially filter both sheets to the selected store.

                          5) Go to the sheet which you want to show the average of all other stores and manually edit the action filter that has been created.  Check the "Exclude" box.

                          6) Voila.

                           

                          See attached coffee sales example.

                           

                          (I didn't know you could do this, by the way - so thanks for asking the question - trying out ways to answer a question is a great way to learn.)