11 Replies Latest reply on Jan 24, 2017 12:16 AM by Norbert Maijoor

# How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

Hey Everyone;

Suppose I have a dimension called KPI and within in it I have 5 KPIs for a Category (another dimension). Each KPI has a different criteria, which means for some the higher the value the better, whilst others the lower the better. How can we create a formula portraying that logic?

Thanks;

Khalid

• ###### 1. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

Hi Khalid,

Upfront!, just as a starter Not very sophisticated but find, a very simple, example as reference below and stored attached workbook version 9.3.

At least a "Classification" and "weighted" component should be included. Both component are of course "arbitrary"

e.g.: Classification:

if [KPI1]<=0.1 then 1

elseif [KPI1]<=0.2 then 2

elseif [KPI1]<=0.3 then 3

elseif [KPI1]<=0.4 then 4

elseif [KPI1]<=0.5 then 5

elseif [KPI1]<=0.6 then 6

elseif [KPI1]<=0.7 then 7

elseif [KPI1]<=0.8 then 8

elseif [KPI1]<=0.9 then 9

else 10

END

e.g. Weighted:

[Class KPI1]*0.2 (this component could be made "variable" based on a parameter) • ###### 2. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

For some odd reason, I could not open the workbook. There was an error in loading the workbook.

Suppose I have 10 different KPIs with values. Each KPI has a different criteria of what is good vs what is bad.

• ###### 3. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

Also I attached sample workbook with some data. Maybe you can show me on that data

Thanks! Really appreciate it!

• ###### 4. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

HI Khalid,

I provided you a workbook in version 9.3. Receiving your test2 workbook it seams you are on a "lower" version?

Which version are you on?

• ###### 5. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

9.2 :/

• ###### 6. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

Hi Khalid,

Find my example attached in workbook version 9.0

I just made the example for 3 kpi's. I assume the "principle" of a possible solution will be clear.

Let me know. • ###### 7. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

Hey Norbert;

Cheers mate! Now I would simply need a long nested if function to sort out the data.

Thanks & appreciate the help!

• ###### 8. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

Hi Khalid,

Your are welcome. Thanks for the "awards":)

• ###### 9. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

Hey Norbert;

Quick question: I am trying to have Max & MIN colour coding for the KPIs, How would I do this?

Regards;

Khalid

• ###### 10. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

//Also I do not want it to shade, but would like the max as a colour and Min as a colour.

• ###### 11. Re: How can create Calculation for Min & Max or Good & Bad for a dimension analyzed at a row base?

Hi Khalid,

Find updated workbook attached

Drag Measure Overall KPI on color tile on Marks-card Probable you would like to check the following viz as well so you can format the individual KPI's