
1. Re: How to calculate a weighted average using the size of a population
Simon Runc Jun 11, 2018 1:46 AM (in response to Muna Abdullah)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

UHC ESA  Weighted Average SR.twbx 170.0 KB

Weighed Average Excel.xlsx 16.8 KB


2. Re: How to calculate a weighted average using the size of a population
Muna Abdullah Jun 11, 2018 4:29 AM (in response to Simon Runc)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 Jun 11, 2018 4:26 AM (in response to Muna Abdullah)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 NonNullvalue 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 Jun 11, 2018 4:53 AM (in response to Muna Abdullah)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 incontext)
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.

5. Re: How to calculate a weighted average using the size of a population
Muna Abdullah Jun 11, 2018 6:08 AM (in response to Simon Runc)Million thanks! Much appreciated.