Linear gauge, set min / max values depending on dimension value, help please

[#1]

Hi,

I have the following pivot table, containing results for a number of different KPIs.

The KPIs 'Actual' and 'Target' values are calculated using formulae which i have stored in variables.

This works fine, results look like this.

Now, i want ot add a linear gauge to the pivot to show how close to the target value we are. I have done this, setting the minimum on the gauge to 0, and the max to the Target value for the KPI, using the following nested IF statement

IF(KPIRef = 'C2', $(vC2Target),
IF(KPIRef = 'C3', $(vC3Target),
IF(KPIRef = 'C4', $(vC4Target),
IF(KPIRef = 'C5', $(vC5Target),
IF(KPIRef = 'C6', $(vC6Target),
IF(KPIRef = 'C7', $(vC7Target),
IF(KPIRef = 'C8', $(vC8Target),
IF(KPIRef = 'C9', $(vC9Target)))))))))

I know this formula works, because i use the same for the Target expression in the table.

When i only have on KPI selected, the linear gauge appears as below:

But when i select more than one KPI, it doesnt like it saying 'Bad min or max value in gauge'

Has anyone else come across this, is it possible to do what i'm trying to do? As i work my way through the KPIs selecting one at a time the gauge seems to update correctly, resetting the max value, but when more than one is picked it errors.

Can anyone advise?

Many Thanks

[johnw · Accepted solution]

The gauge minimum and maximum are set once for the entire table, not once for each row of the table. So if more than one KPI is visible in the table, your IF() expression returns null(), resulting in the error.

The way around it is to divide your column expression by the applicable target to get a percentage (Performance = Actual / Target). Set your linear gauge maximum to a maximum of 1, and it will display the linear gauge like you want to see it.

[#3]

John, thanks again for your help

[#4]

Thank you! This solved my same issue in a minute - I had been struggling with it for hours!

Regards,

Helen

[Anonymous]

Thank you.

What would be the Min set to ?