Straight table calcuations

[Anonymous]

Hi All,

I am trying to achieve the below:

Here are my raw data tables and reference data.

KPI Reference Data:

Metadata table contains information such has KPI name, definition, category and targets.

Data table:

The data table has KPIs data by area for each day.

Calculations:

My final KPI value  is based on the period selection.

• If user select a month (eg: Jan 2017), KPI Value = Sum(Numerator)/Sum(Denominator)
• If user selects a quarter or year, KPI Value = Sum(Numerator for the date range )/Sum(Denominator for the date range )

Same formula applies for each KPI.

Desired Output 1:

I am trying to create  straight tables that summarizes the KPIs by monthly or quarterly, based on the user selecting the periods from the list boxes.

• The table shows number of KPIs met for each area and by category.

                KPI met = if my final KPI value > Target (from KPI ref table)

• The trend arrow compares with the number of KPIs met current vs last period (Month or quarter or Year ...based on user selection).
  

Desired Output 2:

I need to have a summary table as well, which tells the number of KPIs met in each category for whole company, ignoring of the area.

Please can you provide me some direction on how should I model my data and what the variables I need to create to achieve these.

I have attached a sample excel file, that the calculations.

Appreciate your thoughts ?

[adamdavi3s]

This *should* be possible, I wrote up some examples of how to produce a KPI report, its not going to answer all of your questions but it might provide some pointers

Custom KPI Report Using Excel Template and Variables