2 Replies Latest reply on Feb 12, 2016 5:16 PM by Shinichiro Murakami

    How to use Level of Detail calculation to exclude filter

    Nick Shatz

      In the attached workbook I am attempting to show the average value of two products overtime along with an overall average line. Product A and Product B are filtered to only include values between $2.00 and $20.00. My issue is in adding a line to display overall average for each day - I am having trouble excluding the value filter from the overall line.

       

      I have attempted to use {EXCLUDE [Product], [Value Filter] : AVG([Value])} to get overall average for the day while excluding the granularity of product and the criteria of the value filter. This clearly isn't working because adding and removing the value filter affects the overall average. I want the overall average to ignore product and value filter and simply display the overall average value for the day.

       

      Any input greatly appreciated.

       

      Thanks.

        • 1. Re: How to use Level of Detail calculation to exclude filter
          Yuriy Fal

          Hi Nick,

           

          To calculate an Aggregate AVG([overall_avg])

          BEFORE a Dimension Filter on [Value Filter]

          one could use a FIXED LOD.

           

          Please find the attached wb.

          Hope it helps.

           

          Yours,

          Yuri

          1 of 1 people found this helpful
          • 2. Re: How to use Level of Detail calculation to exclude filter
            Shinichiro Murakami

            Nick,

             

            This is good exercise to think about "What is the Average".

             

            Average is always tricky according to aggregation level.

            To say in shortly, (average of average) is different from (total sum/total count).

            Also, one more tricky part is how to handle "null" value.

             

            In your case, I tried three version of calculation shown as below.

             

            [overall avg (modified)]  // Daily average divided by count of product

            {exclude [Product],[Value Filter]: AVG([Value])}

            /{fixed [Country],[Date]:countd([Product])}

             

            [overall avg (copy)] // used Fixed instead of exclude => stable across in/out, but not equal to reference

            {fixed [Country],[Date]: AVG([Value])}

            /{fixed [Country],[Date]:countd([Product])}

             

            [overall avg sum/sum]  //  total value by date divided by total count by date

            {fixed [Date]: sum([Value])}

            /{fixed [Date]:sum([Number of Records])}

             

            The result of comparison shows [overall avg sum/sum] is correct considering reference.

            in different case, different answer should be correct.....

             

            One more thing...

            You need to put "Country Filter" as "Context" to filter effective under LOD calc // Filters and Level of Detail Expressions

             

             

            Thanks,

            Shin

            1 of 1 people found this helpful