Thanks for replying, Channa.
Just averaging doesn't get me a different number.
The Rangeavg expression is very long (below). That's the expression that gets me 50.3 for Wash DC and 42.8 for Norfolk in the table (red circle). That's good. I'm very happy there.
However, in the gauge I've tried the same expression as the KPI and it's returning 43.0 when I'm filtering on those two locations. The average should be 46.6.
I also tried turning the long Rangeavg expression into a variable called "Performance Rating" and then using Avg([Performance Rating]) as the KPI expression for the gauge. No difference. Still returns 43.0.
Any clarity with that information?
THANKS
CM
=Rangeavg(((((Count(distinct[Event Number]))+count(distinct{<[Disposition Name] = {"EXPEDITED REMOVAL-CREDIBLE FEAR (ERCF)"} + {"EXPEDITED REMOVAL (ER)"}>} [Event Number])+count({<[Suspected Gang Affiliation Name] -= {"NONE/NULL"}>} [Event Number])+Count(distinct{<[Incident Detail]={"TSDB (Terrorist Database Hit)/B10"}>} [SIR Number])))), (([Overall Interception Rate])*100), (((count({<[Test Result] = {'Pass'}>} [Test Result])/Count([Test Result])))*100), (((count({<[IOIL Details.Incident Type] = {"POSITIVE SEARCH NO S/A/S (C)"}>} [IOIL Details.Incident Type])+ count({<[Exam Result] = {"POSITIVE"}>} [Exam Result])+Count([CAT I Violation Description])+count({<[CAT II Violation Description] -= {"NEITHER / NONE"}>} [CAT II Violation Description]))/(count([IOIL Details.Incident Number])+count([Exam Result])+count([Result])))*100), (((Count(distinct([Incident Number]))+Sum([Drug Weight (kg)])+count(distinct {<[Property Type Code] = {FEN}>} [Incident Number])+count(distinct {<[Property Type Code] = {HER}, [Drug Weight (kg)]={">=1"}>} [Incident Number])+count(distinct {<[Property Type Code] = {MAR}, [Drug Weight (kg)]={">=500"}>} [Incident Number])+count(distinct {<[Property Type Code] = {KAT}, [Drug Weight (kg)]={">=500"}>} [Incident Number])+count(distinct {<[Property Type Code] = {HAS}, [Drug Weight (kg)]={">=200"}>} [Incident Number])+count(distinct {<[Property Type Code] = {LQH}, [Drug Weight (kg)]={">=50"}>} [Incident Number])+count(distinct {<[Property Type Code] = {COC}, [Drug Weight (kg)]={">=30"}>} [Incident Number])+count(distinct {<[Property Type Code] = {MET}, [Drug Weight (kg)]={">=15"}>} [Incident Number])+count(distinct {<[Property Type Code] = {ICE}, [Drug Weight (kg)]={">=15"}>} [Incident Number])+count(distinct {<[Property Type Code] = {OPM}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {ECS}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {PCP}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {CUR}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {ODB}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {STR}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {OXY}, [Drug Weight (kg)]={">=2"}>} [Incident Number])))), (100-(((Count({<[Incident Detail]={"TSDB (Terrorist Database Hit)/B10", "Aircraft Diversion"}>} distinct [SIR Number])+Count({<[Incident Type]={"Rescue", "Other Agency Assistance", "Significant Agricultural Event"}>} distinct [SIR Number]))*10)-((Count({<[Incident Detail]={"Criminal Allegation Filed", "Exployee Arrested", "Equipment Failure", "Lost/Stolen Property", "Unintentional Discharge", "Detainee Escape"}>} distinct [SIR Number])+Count({<[Incident Type]={"Excessive Wait Time"}>} distinct [SIR Number]))*10))), (100-((Count({<[Incident Type]={"Excessive Wait Time"}>} distinct [SIR Number]))*10)))
