Need help/suggestions for a very complicated gauge visual

[Anonymous]

Hello all,

Warning up front that this post will likely be a bit long...

I need to create a gauge visual for a dashboard that will be viewed by the executive members of my organisation. The gauge itself I know I can setup with relative easy, pick a number range (likely 0-1000 for my purpose) and then it will do the rest. My issue is how I land on the number within that range because I have several unusual "problems" with how I l'd like this to formulate to make this one gauge very meaningful. I will segment my problems into two areas.

To maybe help with context I manage Health and Safety (and its asociated data/reporting) and generally we group things into lead indicators and lag indicators. I need to combine these to provide a visual that shows the "health" of the organisation and its groups. The executive largely has poor understanding of health and safety, and worse they have poor attitude towards its importance. So my objective is to dumb this down and make it a "red bad!" "green good!"... This also may encourage them to take it more seriously if they find themselves lagging behind their peers! On to the problem!

Lead Indicators

My thought for this was a fairly simple one.. Take the number of Full Time Emploees (FTE) and multiply it by 12 to provide a baseline for what "good" is per year. So if I have 100 employees and all 100 employees conduct a "Lead indicator action" I will get 1200 over the year which I will represent as 100% on the gauge. If they conduct over 1200 it will still count the amount but it will only represent as 100%.

Lag Indicators

This is where things become complicated and difficult! The first part which is not all that difficult to fix is that I need to scale each captured lag indicator so for example an incident that results in first aid could have an impact to the score of -3... but a fatality would be -10000.

The next is problem is that I need to design into the equation some way to provide a threshold of a negative score before it starts cumulatively subtracting. The table below is what I would like to achieve and ill explain why..

Incident #	Modifier	Running Score (default starts -10)
Inc 1	-1	-10
Inc 2	-3	-10
Inc 3	-3	-10
Inc 4	-5	-12
Inc 5	-10	-22
Inc 6	-3	-25

The reason I need to do this is that there are some areas within my organisation that don't or they tell people not to report incidents. So when I look at the data and a group has recorded ZERO incidents over a year! That is also a negative sign in the world of health and safety because it means that group has a poor/fearful culture. So I don't want these groups to have high scores based on under reporting.

The end result being (Lead indicators + Lag indicators) = Gauge %

Ideas? thoughts? suggestions?

[AllisonKennedy · Accepted solution]

@Anonymous
Great insightful questions. Ultimately you need to decide what works best for you and makes sense to the exec team. Once you figure out a bit more concretely what weight you want to provide to each metric, you can come back to this forum to seek any help with the DAX calculation.

I think it might be easier to approach this as percentages rather than absolute scores and as you have mentioned, assign a weight to the type of incident. Then count the total reported incidents for each type, divide by the total incidents reported for all types and multiply by their factor of importance for that type (If you have 5 types, the factor of importance should add to 1 or 100%). Add each of these up for each incident type. Perfect score would give 1 or 100%.

To account for under-reporting, would it be feasible to assign a target value, say 1 incident per FTE per year? Then give points if they met or didn't meet the target for each month?