1 Reply Latest reply on Mar 20, 2016 3:58 PM by Bill Lyons

    average function not giving the expected results

    Jesse yung

      Hi, found that average will sum up the field and divide by number of records. However, in attached example, I wanted to get the average of monthly sum, which equals 60, however avg() gives 10 because there're 6 records each month.


      I attempted {fixed [year],[month], sum(sales)} and then average, it gives the desired results, no i'm not sure what does this formula means.


      I also tried {exclude [day],sum(sales)} and then average, that doesn't give the right results though.


      How exactly are these two different, and any other formula possible?



        • 1. Re: average function not giving the expected results
          Bill Lyons

          The formula "{ FIXED  [year],[month]: sum([sales]) }" is known as a Level of Detail (LOD) expression. If you search Tableau's site for Level of Detail expressions, you can find a multitude of documents with detailed explanations and examples. One excellent white paper is http://www.tableau.com/sites/default/files/media/whitepaper_lod_eng_0.pdf.


          Briefly, what the FIXED LOD is doing is "fixing," or locking, the formula at a specific level of detail, in this case the year and month. This means that the sum of sales will be calculated for the year and month, regardless of the fields in the view. So, you are then taking an average of the monthly sum of sales, which is exactly what your goal was.


          The reason { EXCLUDE [day] : sum([sales]) } didn't work for you is that EXCLUDE causes the dimension ([day] in this case) to be excluded from the calculation, but still include all of the fields in the view. However, you don't have [day] in the view anywhere, so it effectively does nothing.


          Are there other formulas? Probably. There are frequently many ways to solve any problem in Tableau. Table calculations are one of them. However, I believe that solution would probably be more complicated and even more difficult to explain.


          I hope that helps.

          1 of 1 people found this helpful