2 of 2 people found this helpful
It's hard to say which one would make sense for you to use without seeing your data - if you can post a sample that would be helpful.
If the [Estimate Annual Rev] and [Net AUM (USD)] each have values at the deepest level of the database, the if you create a calculated field that simply takes the ratio of the two, [Estimate Annual Rev]/[Net AUM (USD)], then you have, effectively, created a new column in the database that contains this ratio for each record. Now, if you place that in viz, and say, summarize it using "AVG" then you would get average ratio across all of the records in the database that meet the criteria of your viz.
If you have data that looks like this:
And you create a calculated field called [UnitRevenue]
[Estimate Annual Rev]/[Net AUM (USD)]
Your data now looks like this:
And then if you remove the [ID] field fromt he rows shelf, so that the viz is forced to "roll-up" all of the IDs and just provide the summary information for all of the measures, and you change the measure calculation for [UnitRevenue] to AVG, you would get the following:
I hope that was somewhat helpful. I think that if you create a small dataset and just try different combinations to see the results, that will be the best way for you to understand what's actually happening.
2 of 2 people found this helpful
I think Kaz gave you the answer you're looking for but I thought I would provide further explanation for each of your examples. This really comes down to a question of how each of your rows is aggregated and the order of operations. I think using [Estimate Annual Rev]/[Net AUM (USD)] will give you what you need. Below is an explanation of how each of your equations work:
AVG([Estimate Annual Rev]/[Net AUM (USD)]) - this is the one that you want. It looks at the ratio of these two numbers row by and row and gives you an average of those ratios. For example AVG(.4 + .5 + .6) = .5. Order of ops: in each row take the ratio of revenue to assets. Take the average of those ratios.
AVG(SUM([Estimate Annual Rev])/SUM([Net AUM (USD)])) - This calculation is not actually possible in Tableau as you are asking it to do two different types of aggregations that are conflicting. In practice it would sum all of your revenue and sum all of your assets and divide. Dividing these two sums will always give you one number. Order of ops: Sum all of revenue, sum all of assets and divide. Take the average of that number...which is just that number.
AVG([Estimate Annual Rev]/AVG([Net AUM (USD)]) - This can also work, but only at the smallest level of granularity. You are essentially finding the average revenue from all of your rows of data and the average of all of your assets, then dividing the two. Filtering and hierarchies will give you misleading numbers in this case. Order of ops: Find the average of all revenue, find the average of all assets, divide one by the other and take the average of that number...which is just that number.
I hope this helps.
Yes. This makes sense. Thank you for the explanation.
Very helpful explanation of the different aggregations. It made everything a lot clearer to me.
I'm assuming this same thinking would apply for all the different types of aggregations (median, weighted averages, etc...)?