
1. Re: Calculating Averages Across Ranges
Jonathan Drummey Jul 19, 2012 5:07 PM (in response to Ben Anderson)Hi,
There are a couple of different ways to approach this. The easiest for Tableau is to have your data in more of a "tall" format (many values) instead of "wide" (many measures). This is because when in a tall format, you can use the builtin SUM,e etc. aggregation across all values very easily, whereas when each age bucket is its own measure then you have to write calculations that include each measure. (Check out the Nonreshaped Data worksheet for how messy the calc is).
I used the Tableau data reshaper (see the link in this excellent KB article on setting up data for Tableau: http://kb.tableausoftware.com/articles/knowledgebase/preparingexcelfilesanalysis) to reshape your data. I also didn't touch the headers, which have an extra character in them.
To convert the age range to a number, I created the "Avg within Age Range" discrete dimension with the following formula:
IF MID([Age Range],3,1) != "" THEN
10
ELSE
INT(RIGHT([Age Range],3))2
END
Then the (weighted) Average Age can be calculated with the following formula:
SUM([Avg within Age Range]*[Value])/SUM([Value])
You can see this in the "Reshaped Avg Age per Org" worksheet.
Calculating the median is a bit more work because it's a weighted median and Tableau doesn't have a builtin function to do that. After building a messier calc, I found a weighted median calc in this post http://community.tableau.com/thread/114874 and created a simplified version that only requires a single formula.
IF FIRST()==0 THEN
WINDOW_MIN(
IF RUNNING_SUM(SUM([Value]))  TOTAL(SUM([Value]))/2 >= 0 THEN
ATTR([Avg within Age Range])
END
)
END
The IF FIRST()==0 part makes sure we won't have any overlapping text. The WINDOW_MIN() extracts the median from the next part of the calc, which does the median calculation and returns the proper value of the Avg within Age Range dimension. This calc will have the Compute Using set to Avg within Age Range.
This requires a view with Org# on Rows and Avg within Age Range on the Level of Detail Shelf. Now, if you also want to show the average age along with the weighted median, then you need a new version of the average age calc that doesn't create overlapping text with the following calc:
IF FIRST()==0 THEN TOTAL([Average Age]) END
This table calc also has its Compute Using set to Avg within Age.
Finally, to show the top 3 ages for each bracket, I created the Index calc using the INDEX() function. This calc has an Advanced... Compute Using set up as follows:
Note that there are two instances of the calc in the view. The first one is a discrete (blue) pill on the Rows Shelf and gets the sort order working right, the second instance is a continuous (green) pill with the same Compute Using settings and set to filter only 13.
Cheers,
Jonathan

Age Range Average.twbx.zip 92.2 KB

Age Range Average.xlsx 11.0 KB


2. Re: Calculating Averages Across Ranges
Ben Anderson Jul 20, 2012 10:59 AM (in response to Jonathan Drummey)Jonathan,
Thank you! The advice was great, but I should have attached my actual dataset. I think the crosstab was a little misleading and I apologize for the confusion. Your formula to return the midpoint worked after some tweaking. I had to change RIGHT to LEFT and obviously then +2. This I think as my fault, if I would have sent you my actual dataset you may have caught whatever problem I was having. Everything is working perfectly now!
Thank you!
Ben

3. Re: Calculating Averages Across Ranges
Jonathan Drummey Jul 20, 2012 12:54 PM (in response to Ben Anderson)You're welcome!