DAX Formula for Calculating Ratios

Alright, I understand now. You want to group the incidents by month from the [Reported_Date] column and then calculate the ratio for each month based on the counts of "Hazard" + "Near Hit" against "Other".

To achieve this, follow these steps:

1. Create Calculated Columns for Month and Year:
   • Create two calculated columns in your 'Incidents_Consolidated' table to extract the month and year from the [Reported_Date] column.

Reported_Month = MONTH('Incidents_Consolidated'[Reported_Date])
Reported_Year = YEAR('Incidents_Consolidated'[Reported_Date])

Calculate the Ratios by Month:

• Create a new table or visualization where you group by [Reported_Year], [Reported_Month], and then apply the DAX formula to calculate the ratio.

Ratio Calculation =
VAR TotalHazardNearHit =
CALCULATE(
COUNTROWS('Incidents_Consolidated'),
'Incidents_Consolidated'[Classification] IN {"Hazard", "Near Hit"}
)

VAR TotalOther =
CALCULATE(
COUNTROWS('Incidents_Consolidated'),
'Incidents_Consolidated'[Classification] = "Other"
)

VAR TotalIncidents = TotalHazardNearHit + TotalOther

RETURN
IF(
TotalIncidents = 0,
"0:0",
IF(
TotalHazardNearHit = 0 && TotalOther > 0,
"0:1",
IF(
TotalHazardNearHit = TotalOther,
"1:0",
ROUND(DIVIDE(TotalHazardNearHit, TotalOther), 1) & ":1"
)
)
)

1. Visualization:
   • Once you have the calculated ratios, you can use a visualization tool (like Power BI, if you're using it) to display the results. For instance, you can create a bar chart where the x-axis represents the [Reported_Month] and [Reported_Year], and the y-axis displays the calculated ratios.

By following these steps, you should be able to calculate the desired ratios grouped by month from the [Reported_Date] column.