1 of 1 people found this helpful
Your KPI formula:
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,
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!
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.
Muthu Krishnan. M
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!
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.
In the attached workbook I have implemented the Average of KPI by the formula:
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.
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.