3 Replies Latest reply on Jan 6, 2017 8:37 AM by Elodie Quirin

# Average of different calculated field - rate calculated differently for KPIs

Hello all,

To begin, I wish you all an happy NEW YEAR 2017 !!

I need help because I'm looking for a solution since 2 days, and I don't find how to calculate what I want.

I want to calculate a global rate for my 5 KPI, with an exception for 1 of all my KPI.

I want to do this :

• RATE for my KPI 1, 2, 4 and 5   = SUM ([KPI Numerateur])/SUM([KPI Denominateur])*100
• > This calculated field is OK for my KPI 1, 2, 4 and 5
• > But I need exception for my KPI 3
• For my KPI 3, I want to calculate the rate differently, so I create a calculated field like this :

IF ATTR([Id Indicateur KPI]) = 3

THEN (((SUM([KPI Numerateur])/[COUNT trigram BU])) / ((SUM([KPI Denominateur]) / [COUNT trigram BU])))*100

END

• At the end, I want to have a global rate for all my KPI 1, 2, 3, 4, and 5.
And I don’t succeed to have the global average, because my KPI 3 is different.

How can I do this ?

Thank you for all your help.

• ###### 1. Re: Average of different calculated field - rate calculated differently for KPIs

To complete my need, I join you my Workbook with my calculated fields :

To do a global AVERAGE, I do like this :

First calculated field for my numerator calculated :

IF ATTR([Id Indicateur KPI]) = 3

Then (SUM([KPI Numerateur])/[COUNT trigram BU])

ELSE SUM([KPI Numerateur])

END

Second calculated field for my denomintor calculated :

IF ATTR([Id Indicateur KPI]) = 3

Then (SUM([KPI Denominateur])/[COUNT trigram BU])

ELSE SUM([KPI Denominateur])

END

And then, I do this :

([Numerateur calculé])/([Denominateur calculé])

But it doesn't work, because, when I bring all my values in my sheet, it just keeps the value for my KPI 1, 2, 4 and 5, but not for the 3.

Thank you a lot for your help.

Elodie

• ###### 2. Re: Average of different calculated field - rate calculated differently for KPIs

Hi Elodie,

It is hard to help because we cannot see what your data looks like. Why wouldn't the following work?

IF ATTR([Id Indicateur KPI]) = 3

THEN SUM ([KPI Numerateur])/SUM([KPI Denominateur])*100

ELSEIF ATTR([Id Indicateur KPI]) = 1 OR ATTR([Id Indicateur KPI]) = 2 OR ATTR([Id Indicateur KPI]) = 4 OR ATTR([Id Indicateur KPI]) = 5

THEN (((SUM([KPI Numerateur])/[COUNT trigram BU])) / ((SUM([KPI Denominateur]) / [COUNT trigram BU])))*100

END

• ###### 3. Re: Average of different calculated field - rate calculated differently for KPIs

Hi Stephen,

Thank you for your help. I've resolved my problem.

Thank you,

Elodie