4 Replies Latest reply on Oct 15, 2013 6:16 AM by John Swimming

    Calculated field with constant custom level of aggregation (even without LoD)

    John Swimming

      Hi friends!

      I am new to the forum, and I am loving Tableau.

      I've been 2 days struggling with this simple thing as I am just starting with Tableau... I'm sure it has to be straightforward!!


      Sorry for the following example, but I have no iexplorer at home, and so I cannot do a sample tableau. Att work time I am now completly busy and have no time so I take profit on my free time:

       

      This case is what I want to figure out:

       

      Country   State        City    AVG(Revenue) AVGof[AVG(Revenue) at City Level]

      USA    New hampshire  Dover       10               10

      USA    New Hampshire  Manchester  15               15

      USA    New Hampshire  Concord     20               20

      USA    Florida        Orlando     20               20

      USA    Florida        Tampa       30               30

      UK     England        Dover       30               30

      UK     England        Manchester  40               40

         

       

      Country   State                AVG(Revenue) AVGof[AVG(Revenue) at City Level]

      USA    New hampshire              15               15

      USA    Florida                    25               25

      UK     England                    35               35

       

       

      Country                        AVG(Revenue) AVGof[AVG(Revenue) at City Level]

      USA                               19*              20**

      UK                                35               35

       

      *Avg of 10,15,20,20,30 = 95/5 = 19

      **Avg of 15 and 25

       

       

      I am obliged to show only Country + both AVG measures. So I need to play with Level of Detail.

       

       

      I would like to understand how to solve both approaches, with Level of Detail = Country, and with Level of Detail = City

       

      I learnt well WINDOW functions and this is what happens

       

      * If I am at Country level, I cannot specify City as Level for the WINDOW_SUM function, it is not selectable in the drop-down box


      * If I am at City Level, the values are like usually not added when aggregated but shown in row!!!   So I get something like AVG = 15 25

       

      So at the end is like asking a constant value, that is not affected whether you are a level above or below, since it calculates internally

      .

      How would you solve both cases!!

      I would appreciate your help