5 Replies Latest reply on Jan 20, 2013 5:19 AM by insarmukhamedzhanov

    Average of sums or sum of averages?

    insarmukhamedzhanov

      Hi!

       

      I have date of a simple game: users in different levels buy different products. I have summarized all spendings by levels 1st graph), counted unique users on each level (2nd graph) and calculated average spendings per user on a level (3rd graph).

      The problem begins when I try to get average spending per user on a level by types of products (4th graph). Obviously it differs from average spendings without grouping by product types, because we calculate average within each product type. And I need the same graph as 3rd is but with product types stacks.

       

      How could I calculate the sum of product types divided on total number of unique users on a level (and not sum of each product type divided on users who bought this type on a level). Or even what i need to read to make this calculation?

       

      Please see twbx file enclosed, thanks a lot in advance!

        • 1. Re: Average of sums or sum of averages?
          Dana Withers

          Hello!

           

          I'm only just starting Tableau myself, but it was a nice question to play with and find out more about the possibilities.

          I hope I'm understanding correctly, though I may be wrong. I noticed that your third graph is showing average spending by non-unique users. Average spending by unique user is 7.04 or something.

          If you want to get the same graph as 3rd but with product types colouring parts of the stack then are you not looking for something like... show a number that represents the average spending (6 or 7 depending on (non-)unique users), but coloured according to how much as a percentage of that average spending is done by each product type?

          I got close by calculating ([average spending]) * ([count entries with product type] / [all entries in the level]).

          I say close because I could not get all rows in the level... However that is quite probably an easy data point to get straight from your source. I found a post but cant find it anymore regarding custom SQL to get your data that suggested a solution to include such a total. Or perhaps if the level is not a column, but a row it may be easier to get row total?

           

          Hope it helps!

          1 of 1 people found this helpful
          • 2. Re: Average of sums or sum of averages?
            Alex Kerin

            Hello Insar, and good job Dana. As Dana is alluding to, it's the distinct count over the whole level that you cannot get. Look at the second sheet and see if the calculation I added provides the number you would expect per user level.

             

            Whatever we do here, we will be using table calculations - in this case total(countd([users])

             

            The results of table calculations change depending on how you you set up the partitioning. In this case I think (though I don't know) we want to create a partition with user level.

            1 of 1 people found this helpful
            • 3. Re: Average of sums or sum of averages?
              Gowtham P

              Hi,

               

              Are you looking for Sum (Avg (Measure)) ?

               

              1. So Create a calculated fied Window_sum(Average(Measure)).

              2. If you are planning to get this sum of avg measure at "User Level", pull this field to "Level of Detail" section.

              3. Right click on the measure --> compute using "User Level".

               

              The the measure will calculate at the level and will be displayed in the graph. Hope this helps.

              • 4. Re: Average of sums or sum of averages?
                insarmukhamedzhanov

                Dear Dana, thanks a lot for helping. Yep, there was my mistake in the 3rd graph, because I wanted to get average among unique users.

                • 5. Re: Average of sums or sum of averages?
                  insarmukhamedzhanov

                  Alex, thank you very much, that's what i need!

                  I also have understood that I need to read manual about table calculations carefully. Hope this will help to get what i need on the topic.