1 Reply Latest reply on Aug 10, 2016 6:35 AM by Tom W

    Detect prices higher than (average+1,1average)

    Federico Armentano

      Hi all, I database that contains 2 fields: Product ID and Price.


      I would like to detect wich products are higher than 0,1 times the average.


      In the excel attached you will find the result expected. I have problem because when I write


      IF price> avg(price)*1,1 then "see" it is not working because de level of aggregation. I think with a LOD It can easily fix it for most of you.



        • 1. Re: Detect prices higher than (average+1,1average)
          Tom W

          If you just use a calculation like IF [price] > AVG([price]) and you include the product / order number on the rows, then its going to run your aggregate calculation (AVG) in the context of that row.


          So, you are correct in that you need an LOD, but you might also want to use a WINDOW_AVG instead.


          For example; SUM(Sales) > WINDOW_AVG(SUM(Sales))

          This uses the WINDOW_AVG Table Calculation which you would need to configure to make sure it calculates the Window_Avg on the partitioning correctly (i.e. Table (Down) or Table (Across)

          The benefit of this approach is it will calculate an average for whatever Products are currently in scope of the filter.


          If you want to find the average for all products regardless of filters, then a LOD is the right approach;

          SUM(Sales) > SUM({FIXED [Country] : AVG(Sales)})


          You need to 'fix' it to a higher level of aggregation i.e. region or country.