1 2 Previous Next 17 Replies Latest reply on Dec 30, 2013 12:11 PM by Ramon Martinez

# How do I calculate the weighted average on a group of data in tableau?

Hi, I need help calcualting a weighted average for all of my data. I am trying to write a formula that takes the total sum of the avergae TOW completion * number cases / the number of cases. I have created a formula but it doesn't seem to give me what I want. Help!

Message was edited by: Tiffany Batton

• ###### 1. Re: How do I calculate the weighted average on a group of data in tableau?

Hello Tiffany,

Please provide a workbook with sample data to see if we are able to figure out a solution for you.

Best,

Ramon

• ###### 2. Re: How do I calculate the weighted average on a group of data in tableau?

Hi Ramon,

My apologies. I thought I attached the workbook in my original post. It is there now. Thanks for your help!

• ###### 3. Re: How do I calculate the weighted average on a group of data in tableau?

·         Hi Ramon,

My apologies. I thought I attached the workbook in my original post. It is there now. Thanks for your help!

Tiffany Batton

Metrics and Analytical Consultant

Northwestern Mutual - Enterprise Solutions

• ###### 4. Re: Re: How do I calculate the weighted average on a group of data in tableau?

Hi Tiffany,

I did some minor changes to see if I'm understanding well your requirements.

Your original formula for Weighted Average is calculating the Total Weighted Average. See the Grand Total at Sheet 2.

I created an additional calculated field called Weighted Average new with the formula:

```SUM([Average TOW Completion Age]*[Number of Cases])/TOTAL(SUM([Number of Cases]))
```

computed using Table (Down), this means that its value are calculated respect the Grand Total of Number of cases. See that the Grand Total of Weighted Average new is similar to Weighted Average , but with my formula it is possible to calculate the Weighted Average per row of the table.

Changing Compute using Pane, for instance, allows to calculate the weighted average of every row in the table respect to the subtotal of every pane defined by the dimension TOW (Type of Work)

take a look and let me know if this shed some light to reach your solution.

Best

Ramon

• ###### 5. Re: How do I calculate the weighted average on a group of data in tableau?

Hi Tiffany, I'm not sure exactly what the results should look like or what you are trying to achieve like but try the following:

AVG([Average TOW Completion Age])*(sum([Number of Cases]))/TOTAL(SUM([Number of Cases]))

set the calculation direction to Table(down)

Then add a column grand total. That Grand Total should now be the overall weighted average.

If you want to calculate weighted averages by sub-total at SOW level then using Compute Using -> Pane (Down)

• ###### 6. Re: How do I calculate the weighted average on a group of data in tableau?

Doh! I was doing this at exactly the same time as you Ramon

• ###### 7. Re: How do I calculate the weighted average on a group of data in tableau?

Hi Ramon,

this is very helpful. I have another question. How would I show the weighted average per type of work (tow) or in a bucket?

• ###### 8. Re: How do I calculate the weighted average on a group of data in tableau?

Thanks Peter!

• ###### 9. Re: How do I calculate the weighted average on a group of data in tableau?

Hi Peter,

I'm here just having fun, studying and practicing for my Certification Exam with real problems provided by members of the community. Good to see your contributions to the community.

I liked your Workbook Library post today.

Warm regards

Ramon

• ###### 10. Re: How do I calculate the weighted average on a group of data in tableau?

You have tow options here:

2.- Remove the dimension TOW Detail from Rows shelf

Let me know if that works for you

Best

Ramon

• ###### 11. Re: How do I calculate the weighted average on a group of data in tableau?

I'm wondering if none of this is needed, just using average at the right level should do exactly the same thing.

• ###### 12. Re: How do I calculate the weighted average on a group of data in tableau?

this is definitely the case, use AVG(Average TOW Completion Age) and that will give you weighted Average TOW Completion Age. Then just put in dimensions as you see fit to break into smaller groups.

• ###### 13. Re: How do I calculate the weighted average on a group of data in tableau?

Thanks Ramon, getting in early on my resolution for 2014 to get make more effort on the forums

• ###### 14. Re: How do I calculate the weighted average on a group of data in tableau?

You are right Peter. I'm not understanding well the meaning of Average TOW Completion Age as weight.