Is there a way that I could get the average of an aggregated calculated field? For example, I have to get the KPI value, hourly and daily. To get the hourly KPI value, I just need to have a calculated field that looks like this (SUM(numerator)/SUM(denominator) = hourlyKPI).

For better understanding, see example below:

Date

Numerator

Denominator

HourlyKPI (numerator/denominator)

September 2, 2013

73,652,761

77,863,348

94.59%

September 3, 2013

93,991,656

99,148,793

94.80%

September 4, 2013

255,935,818

270,673,049

94.56%

September 5, 2013

254,837,665

270,399,691

94.24%

September 6, 2013

251,146,578

267,802,542

93.78%

September 7, 2013

269,358,841

288,202,100

93.46%

September 8, 2013

264,722,167

281,225,469

94.13%

September 9, 2013

249,764,652

264,189,274

94.54%

Now the tricky part is this, to get the KPI value per day the requirement is to get the average of hourlyKPI. The problem is, hourlyKPI is already aggregated, there's no way for me to get its average.

For better understanding, I need to compute the dailyKPI like this.

AVG(94.59%, 94.80%, 94.56%, 94.24%, 93.78%, 93.46%, 94.13%, 94.54%)

Does anybody know of a solution or at least a workaround? We are currently in the middle of a project. Thank you.

give the value of KPI by unit of time, in your specific scenario where you are using Date.

The only thing you need to do is to change the time unit, I mean, if you want to knoe the value of KPI per year, you have to use YEAR([Date]), in the case of Quarter QUARTER([Date]), MONTH([Date]) per month and so on. The same formula will give the average per unit of time.

I'm attaching a workbook that demonstrate the case

The worksheet shows Sales, Profir and KPI calculated as SUM([Profit])/SUM([Sales]) per different units of time using the dimension [Order Date]

See that the column KPI will give the correct value per unit of time. For instance the values of KPI in January, February and march are 0.12 , 0.09 and 0.06 the value of KPI for Q1 is the average of those 3 values such as (0.12+0.09+0.06)/3=0.27/3=0.09

So if you remove the pill MONTH(Order Date) from Rows shelf you will see that the KPI for Q1 is 0.09

The calculation you made is actually the conventional way of computing it. Your computation is equivalent to my computation for the hourlyKPI ((SUM(numerator)/SUM(denominator) = hourlyKPI), However, I really need 2 calculations: hourlyKPI and dailyKPI.

For the daily KPI, it may seem more logical to get the SUM of SALES for the whole week, month or day then divide it by the SUM of PROFIT for the whole week, month or day to get the KPI. But in my case, it's different. I need to get the KPI per day then get the average.

In the attached workbook I have implemented the Average of KPI by the formula:

WINDOW_AVG([KPI],FIRST(),LAST())

Computed by Pane

This formula calculate the average of KPI per time unit. In the case of the attached workbook is the average of KPI per moth in an specific Quarter. You will realize that the result is exactly the same as I explained in my previous post.