Scoring System from Counts

Power BI Data Model
1

Hi Team,

I am busy with Telemetry data from vehicles i.e. trip data.
For each trip we get a total for each category below (Behavioural)

Harsh Acceleration Count
Harsh Braking Count
Harsh Cornering Count
Overspeeding Counts
Impact Alerts

Business Requirement
Which drivers do we need to increase their monthly premium for unsafe driving?

We are not looking at no of trips or drive time, distance etc. If a driver is driving safe - we are happy and may decrease premiums.
Our aim is to identify the drivers that are not driving safely and to increase premiums accordingly.

So I want to go from a count of the above to a score
In its most simple form the higher the counts for lower your score should be.

Ideally I would like to have all drivers start off with a score of 100 and decrease in relation to the Total weighted behavior Counts

The problem I am running into is the due to some drivers getting high counts for the Overspeeding Count the score ends up in the negative and I cannot think of a dynamic way to handle this.

If you have any suggestions or even a complete shift in direction with a better scoring method please let me know your thoughts.

Note: I am not looking for a DAX answer, I would like to get some idea’s - discuss a solution conceptually. I want to have a plan then most definitely dive into the DAX

Awesome, thank you.

2

Hi KyleJ,
Simplest solution may be start the score from 0. When driver does unsafe driving increase the penalty score. So then you do not need to limit to 100. So driver not reducing his points. Driver is adding a penalty score starting from zero.

Thanks,
Akila

3

Thanks Akila, I hear you and I have gone that way a bit - I have Ranked the drivers based off the weighted behavior counts and also added a Ranking Group (Percentile)

This works - you can see what Rank you are and What that Ranking Means i.e Top 20% etc.

This scoring system of starting with 100 was from the guy before me, and its cool because I don’t have any plan so its something but it has it flaws. This is what is currently used and this is what’s expected.

I hear your logic and If no other options I will chat with the business owner and discuss how to adopt and implement the new approach. (High Score is bad - Low score is good, Bottom 20 %)

4

a couple of ideas occur to me -

1. what about setting a maximum negative score for each item

  • you have 5 criteria, so don’t allow any of them to count for more than 20% of the total,
  • if they hit the maximum amount for an item, then just return the max for your count
  • this may need to be adjusted if (for instance) Impact Alerts are considered worse than Harsh Braking Count

2.Build up your count as suggested by @akila789, then adjust to your 100 limit

  • Step 1 - get max of all calculated scores
  • Step 2 - divide that max amount by 100
  • Step 3 - multiple all calculated scores by the amount in Step 2
  • Step 4 - subtract the amount from Step 3 from 100
1 Like
5

Hi @KyleJ did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

6

Hi @KyleJ, we’ve noticed that no response has been received from you since the 25th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!