5 Replies Latest reply on Jun 11, 2018 6:08 AM by Muna Abdullah

    How to calculate a weighted average using the size of a population

    Muna Abdullah

      Hi,

      Appreciate if anyone can help on factoring in population size when calculating regional average for countries. For example universal health coverage (UHC) of Botswana is 60% and its population 2.2 million on the other hand UHC of Ethiopia is 39% and its population is 100 million. We have 23 countries in East and Southern Africa (ESA) region. I was trying to figure out the UHC for ESA region on Tableau. I know it is possible to do a weighted average on excel but I am wondering if you can help. I tried the reference line which gives me the average (see workbook below)

        • 1. Re: How to calculate a weighted average using the size of a population
          Simon Runc

          hi Muna,

           

          So I think this calculation should do the trick

           

          [UHC Service Coverage - Weighted Average All Countries]

          {SUM({FIXED [Country name]: SUM([Population (in thousands)] * [UHC index of essential service coverage (%) - latest available year])})}

          /

          {SUM([Population (in thousands)])}

           

          I've done it as an LoD, so you can use it regardless of the level of detail in your Viz. In the 'how it works' tab, you'll hopefully see what it's doing. We are creating the %age of people covered for each country and then summing these up and dividing by total propulation (summed)

           

          I've also just about retained enough memory (from long ago) on how you'd do this in Excel, to verify the calculation.

           

          Hope that helps, and makes sense

          • 2. Re: How to calculate a weighted average using the size of a population
            Muna Abdullah

            Hi,

            Many thanks! The calculation is accurate. I have checked it also on excel. I was trying to understand the Tableau function. I see the formula as on the analytics

            On the how it works

             

            Which shows 63% global average. Great!!

            Is it possible to insert the calculation on the reference line?

            I tried to do the same calculation for 23 countries in ESA region (see below) the weighted average remains the global average. Sorry for asking too many questions.

            • 3. Re: How to calculate a weighted average using the size of a population
              Simon Runc

              Cool...glad it made sense.

               

              Yes you can make this a reference line...if you add a green pill to the detail pane you have access to it in the Reference Line set up. However we need to make a few tweaks to make this work in your viz.

               

              Firstly I need to make the Non-Null-value filters on UHC and GGHE context filters (LoDs are computed before any regular filters are applied, so to exclude these rows from the calculation we need to bump them up the calculation pipeline, so they affect the LoD). As I can't make aggregated filters context ones, I just needed to change these to dimensions (this has the same effect). There are other ways we could do this (but this is the simplest)

               

              I also had to multiple the LoD by 100...as you seem to have multiplied your %ages by 100!

              • 4. Re: How to calculate a weighted average using the size of a population
                Simon Runc

                No problem. So this is the same "context" filter issue I mentioned in the Reference Line post.

                 

                Any filters that you want to affect this average, will need to be made "in context" filters (you can find that option by clicking on a filter and selecting "add to context"...it'll also go brown/gray in colour to let you know that it's now in-context)

                 

                I went with the LoD option, as I don't know where else you are going to use this calculation, but here is an alternative way (where any filtering will be reflected in the average, without needing to add filters to context

                [Population with UHC Service Coverage]

                [Population (in thousands)]*[UHC index of essential service coverage (%) - latest available year]

                 

                 

                [UHC Weighted Coverage - AGG]

                (SUM([Population with UHC Service Coverage])

                /

                SUM([Population (in thousands)]))

                *100

                 

                I have added both to the Chart (as Reference Lines) and made all your filters, in context...if you remove the context from the filters you can see the difference in filter behavior between a regular aggregate and a FIXED LoD.