Forum Discussion
Calculating a score from workplace accident data
Hi,
I have a table which is below that shows data of workplace accidents and seriousness:
| Company ID | Year | Accident Level | number of accidents |
| 2000 | 2023 | Very Serious | 2 |
| 2000 | 2023 | medium | 4 |
| 2000 | 2024 | medium | 1 |
| 2001 | 2023 | very low | 6 |
| 2001 | 2024 | medium | 2 |
| 2001 | 2025 | medium | 1 |
I set an ID for each level of accidents, 1 for very low, 5 for very serious.
I want to calculate a risk score for each company depends on several things,
1) score weigh: for each level, score point will be different. and also, if a level of accident happened previous year, current year's score points will be different.
2) year: 2025 score points will be higher than 2024 and 2024 will be higher than 2023.
for example:
| 2025 | |
| points if not repetitive from 2024 | points if repetitive |
| 1- 0.1 | 0.2 |
| 2- 0.5 | 1 |
| 3- 1 | 1.5 |
| 4- 1.5 | 2 |
| 5- 2 | 3 |
| 2024 | |
| points if not repetitive from 2023 | points if repetitive |
| 1- 0.1 | 0.1 |
| 2- 0.3 | 0.5 |
| 3- 0.8 | 1 |
| 4- 1.2 | 1.5 |
| 5- 2 | 2.5 |
I would put the total score to a Card visual, when a company is selected in slicer, that visual would show the total score.
I tried to use Chat GPT but no use, i need to learn how to write correct prompts.
Any help is appreciated.
- Create mapping table (AccidentLevel → ID).
- Create scoring table (Year, LevelID, Repetitive, Points).
- Add column in accident table to check if same level happened last year → "Yes"/"No".
- Relate Accident table to Scoring table (Year + LevelID + Repetitive).
Measure:
Risk Score =
SUMX (
'Accidents',
'Accidents'[NumAccidents] * RELATED('Scoring'[Points])
)
Show [Risk Score] in a Card with Company slicer.
3 Replies
- FarhanJeelani
Super User
Hi Anaxielia1 ,
Please follow the steps below.
1. Identify RepetitionFor each accident record, check if the same company had the same AccidentLevelID in the previous year.
IsRepetitive =
VAR CurrentCompany = Accidents[CompanyID]
VAR CurrentYear = Accidents[Year]
VAR CurrentLevel = Accidents[AccidentLevelID]
VAR PreviousYear = CurrentYear - 1
RETURN
CALCULATE(
COUNTROWS(Accidents),
FILTER(
Accidents,
Accidents[CompanyID] = CurrentCompany &&
Accidents[Year] = PreviousYear &&
Accidents[AccidentLevelID] = CurrentLevel
)
) > 02. Fetch the Correct Points Per Accident
Create a measure to fetch the correct points based on repetition and year:
AccidentPoints =
VAR IsRep = [IsRepetitive]
VAR Y = SELECTEDVALUE(Accidents[Year])
VAR L = SELECTEDVALUE(Accidents[AccidentLevelID])
RETURN
CALCULATE(
IF(IsRep,
MAX(LevelPoints[RepetitivePoints]),
MAX(LevelPoints[NonRepetitivePoints])
),
LevelPoints[Year] = Y,
LevelPoints[AccidentLevelID] = L
)3. Calculate Total Score per Company
Multiply points per accident by the number of accidents, and then sum for the selected company:
TotalRiskScore =
SUMX(
Accidents,
[AccidentPoints] * Accidents[NumberOfAccidents]
)Link your slicer to CompanyID—when you select a company, the Card visual will show [TotalRiskScore] filtered just for that company.
Please mark this post as solution if it hepls you. Appreciate Kudos.
Hi,
I cant create VAR's like this, it only shows measures after equal sign.
VAR CurrentCompany = Accidents[CompanyID]
- Shahid12523
Community Champion
- Create mapping table (AccidentLevel → ID).
- Create scoring table (Year, LevelID, Repetitive, Points).
- Add column in accident table to check if same level happened last year → "Yes"/"No".
- Relate Accident table to Scoring table (Year + LevelID + Repetitive).
Measure:
Risk Score =
SUMX (
'Accidents',
'Accidents'[NumAccidents] * RELATED('Scoring'[Points])
)
Show [Risk Score] in a Card with Company slicer.
