4 Replies Latest reply on Mar 30, 2018 8:17 AM by Jonathan Drummey

    Weighted Stats (median, mean, percentiles)

    chris guth

      I have a dashboard that displays stats for a list of fees (25th percentile, median, average, 75th percentile, count). It works fine except I now need to weight the data (by WEIGHT). The data needs to be weighted my member group. I've been reading some posts and articles but no luck getting it to work. The closest i came was ACS Data--SPSS & Tableau Generating Different Medians  and Weighted Medians for Weighted Data in Tableau — DataBlick  but could not the the correct figures. Any help with this would be greatly appreciated.

       

      Thanks!

      Chris

        • 1. Re: Weighted Stats (median, mean, percentiles)
          Patrick A Van Der Hyde

          Hello Chris,

           

          Do you have an example of an expected value?  And I wasn't sure what the member groups meant. 

           

          Jonathan Drummey is the author of the piece you mention on the Data Blick site so I am mentioning him as well since he may want to be aware of any information that might make the article more helpful.

           

          Patrick

          • 2. Re: Weighted Stats (median, mean, percentiles)
            chris guth

            Hi Patrick,

             

             

             

            Thanks for getting in touch. I have been mostly successful in adapting Jonathan’s calc fields and using them in my dashboard. I also modified them a bit to produce 25th and 75th percentiles. Currently I am weighting the data by member type(member type 1 weight = 0.25,  member type 2 weight = 0.39;  member type 3 weight = 2.58 ).  If I filter by multiple data types things seem to be working fine, but if I select only a single member type in the filter I sometimes get 2 values as the median (see below)

             

             

             

             

             

             

             

             

             

            Interestingly enough ,the median value I get from SPSS a number between those 2 values (e.g. 33 & 34 above for item 4, but 33.5 in SPSS).  What I ended up doing was utilizing 2 sheets in Tableau. The 1st sheet gets used if multiple member types are selected in the filter (using the weighted median calculations.) If the user selects a single member type in the filter a 2nd sheet is used with uses the MEDIAN Tableau function. Since a single membership type is being filtered I don’t have to apply a weight in the calculation of the median. I would still like to figure how to get the weighted median calc filed to work properly for my purpose. Since I am applying the weighted median calculation to multiple items I am also trying to figure out how to use either the “mean” interpolation or the “high” interpolation depending on whether the median value falls between 2 data points. I hope this make sense.

             

             

             

            Thanks,

             

            Chris

            • 3. Re: Weighted Stats (median, mean, percentiles)
              chris guth

               

              This pic goes with my reply above. You can see how item 4 shows 2 values for the median rather than just one.

              • 4. Re: Weighted Stats (median, mean, percentiles)
                Jonathan Drummey

                Hi Chris,

                 

                I'd seen your earlier post as well and haven't had a chance to get back to this, I'll take a look over the weekend.

                 

                Jonathan