7 Replies Latest reply on Nov 8, 2013 8:55 PM by Ramon Martinez

# How to get the average of an aggregated calculated field?

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:

DateNumeratorDenominatorHourlyKPI (numerator/denominator)
September 2, 201373,652,761
 77,863,348
 94.59%
September 3, 201393,991,656
 99,148,793
 94.80%
September 4, 2013255,935,818
 270,673,049
 94.56%
September 5, 2013254,837,665
 270,399,691
 94.24%
September 6, 2013251,146,578
 267,802,542
 93.78%
September 7, 2013269,358,841
 288,202,100
 93.46%
September 8, 2013264,722,167
 281,225,469
 94.13%
September 9, 2013249,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.

• ###### 1. Re: How to get the average of an aggregated calculated field?

Hi Christopher,

SUM([Numerator])/SUM([Denominator])

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

I hope this helps,

Best

Ramon

1 of 1 people found this helpful
• ###### 2. Re: How to get the average of an aggregated calculated field?

Hi Ramon,

Thanks for a quick response, I really appreciate it.

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.

AVG(KPIofDayOne, KPIofDayTwo, KPIofDayThree, etc)

Just inform me if you need more clarifications on this.

Thanks pal. Appreciate it!

• ###### 3. Re: How to get the average of an aggregated calculated field?

Hi Christopher,

Create an another calculated field, in that select your function ex. AVG and then select your aggregated calculated field in the field. I tried that it works.

Regards,

Muthu Krishnan. M

• ###### 4. Re: How to get the average of an aggregated calculated field?

Hi Muthu,

Do you have a twbx for that? Because, I did it but did not work. It says, "Argument to AVG is already and aggregation, and cannot be further aggregated."

Thanks a lot!

• ###### 5. Re: How to get the average of an aggregated calculated field?

Hi Chrsitopher,

To help you in a better way, I think it is better if you post you workbook with sample data, including ate least one worksheet with configuration of the result you need to get.

Best

Ramon

• ###### 6. Re: Re: How to get the average of an aggregated calculated field?

Hi Christopher,

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.

See attached workbook.

Best,

Ramon

• ###### 7. Re: How to get the average of an aggregated calculated field?

HI Christopher,

Have you review the workbook I included in my previous post?

Take a look at it and let me know if it reaches your requirement.

Best

Ramon