2 Replies Latest reply on Oct 31, 2016 2:31 AM by Winson Lui

    Percentage of days which the average value on each date falls below a value across a period of time

    Winson Lui

      Hi,

       

      I have a dataset which contains date, product type, country and revenue and another excel spreadsheet which contains only the revenue benchmark figure, say $1000.

      I would like to see the percentage of days of each product’s average revenue falls below the benchmark across the whole year.

       

      For example, if product A had a revenue of £1500 from US and £100 from UK on 1/1/2016, the average revenue on that particular date would be (1500 +100) /2 = $800 which is below the benchmark.

       

      The equation should be count of all these date having the average revenue below the benchmark divided by the total count of date having a revenue value reported.

       

      I tried to create a calculated field which examines whether the average is higher/ lower than the benchmark and then divided the count of lower by the count of date. However, the result I received is based on each individual data point instead of the average value on a date even I put a syntax of fixed date before the actual calculation.

       

      Would you please let me know what wrong with my logic/ programme?

      1. Thanks.