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

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.
• ###### 1. Re: Percentage of days which the average value on each date falls below a value across a period of time

Hey Winson,

I believe you would want to create a Boolean which checked if a date met the threshold. If you provide us with a packaged workbook, we could take a closer look and try to give a specific answer.

If you've already found a solution, please share for other users to find and use!

-Diego

• ###### 2. Re: Percentage of days which the average value on each date falls below a value across a period of time

Hi Diego,

I still haven't got any solution in my mind.

Please see the attachment for your reference.

I would like to calculate the percentage of no. of days to the whole period which the average hard bounce rate is above the benchmark 0.5%.

Thanks.