5 Replies Latest reply on Oct 24, 2016 6:20 AM by Torey Weiss

    Differences in Calulations

    Torey Weiss

      Hi All,

       

      I'm trying to understand the difference between what a few calculations means. For example, to get the fee paid, we would divide the revenue by the assets:

      [Estimate Annual Rev]/[Net AUM (USD)]

       

      However, to get the average of this would it be:

       

      AVG([Estimate Annual Rev]/[Net AUM (USD)])

      or

      AVG(SUM([Estimate Annual Rev])/SUM([Net AUM (USD)]))

      or

      AVG([Estimate Annual Rev]/AVG([Net AUM (USD)])

       

      Would there be a difference if I were to drill down into further categories of region or client?

      Which is the correct one to use? Would this also apply to the MEDIAN calculation?

       

      Thanks for the help!

      Torey

        • 1. Re: Differences in Calulations
          Kaz Shakir

          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]

           

          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
          • 2. Re: Differences in Calulations
            Wesley Magee

            Torey,

            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.

            -Wesley

            2 of 2 people found this helpful
            • 3. Re: Differences in Calulations
              Torey Weiss

              Yes. This makes sense.  Thank you for the explanation.

               

              -Torey

              • 4. Re: Differences in Calulations
                Torey Weiss

                Very helpful explanation of the different aggregations. It made everything a lot clearer to me.

                • 5. Re: Differences in Calulations
                  Torey Weiss

                  I'm assuming this same thinking would apply for all the different types of aggregations (median, weighted averages, etc...)?