
1. Re: How to get the average of an aggregated calculated field?
Ramon Martinez Nov 7, 2013 10:15 PM (in response to Christopher Anthony Castro)Hi Christopher,
Your KPI formula:
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

KPI per unit of time.twbx 1.2 MB


2. Re: How to get the average of an aggregated calculated field?
Christopher Anthony Castro Nov 7, 2013 10:34 PM (in response to Ramon Martinez)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?
muthu.krishnan Nov 7, 2013 11:04 PM (in response to Christopher Anthony Castro)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?
Christopher Anthony Castro Nov 7, 2013 11:47 PM (in response to muthu.krishnan)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?
Ramon Martinez Nov 7, 2013 11:53 PM (in response to Christopher Anthony Castro)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?
Ramon Martinez Nov 8, 2013 12:18 AM (in response to Christopher Anthony Castro)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

KPI per unit of time.twbx 1.2 MB


7. Re: How to get the average of an aggregated calculated field?
Ramon Martinez Nov 8, 2013 8:55 PM (in response to Christopher Anthony Castro)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