DAX Formula for Calculating Ratios

[Anonymous]

Table Name is ‘Incidents_Consolidated’.

Column Names are [Reported Date] and [Classification]

The data in the [Reported Date] Column is (mm/dd/yyyy)

The data in the [Classification] column is text and either “Near Hit”, “Hazard” or “Other”.

What is the DAX formula for calculating:

Each Month from {Reported Date]

If [Hazard] + [Near Hit] + [Other] = 0 then “0:0”

If [Hazard] + [Near Hit] = 0 and [Other] > 0 “0:1”

If [Hazard] + [Near Hit] = [Other] then “1:0”

Else

Round(Divide(([Hazard]+[Near Hit]),[Other]),1) & “:1”

[Anonymous]

OK, thanks for persevering with me abc123,

I have a table - 'Incidents_Consolidated'

the Coumns in the table that I'm trying to calculate are:

[Reported_Year], [Reported_Month] and [Classification]

The [Classification] Column contains data that is either:

"Hazard", "Near Hit" or "Other"

I want to calculate the ratio of "Hazard" + "Near Hit" to "Other" for each [Reported_Month] of the [Reported_Year]

Using the calculation:

F (
TotalHazardNearHit + TotalOther = 0,
"0:0",
IF (
TotalHazardNearHit = 0 && TotalOther > 0,
"0:1",
IF (
TotalHazardNearHit = TotalOther,
"1:0",
ROUND ( DIVIDE ( TotalHazardNearHit, TotalOther ), 1 ) & ":1"

Many thanks

[123abc]

To create a DAX formula for calculating the ratios based on the conditions you provided, you can use the following formula. Assuming you have columns named [Reported Date], [Classification], [Hazard], [Near Hit], and [Other], the formula would look like this:

Ratio =
VAR TotalHazardNearHit = [Hazard] + [Near Hit]
VAR TotalOther = [Other]
RETURN
IF (
TotalHazardNearHit + TotalOther = 0,
"0:0",
IF (
TotalHazardNearHit = 0 && TotalOther > 0,
"0:1",
IF (
TotalHazardNearHit = TotalOther,
"1:0",
CONCATENATE (
ROUND ( DIVIDE ( TotalHazardNearHit, TotalOther ), 1 ),
":1"
)
)
)
)

This formula uses variables (TotalHazardNearHit and TotalOther) to make it easier to reference the sum of [Hazard] and [Near Hit] and [Other] in the formula. It then checks the specified conditions and returns the appropriate result based on those conditions.

Please replace [Hazard], [Near Hit], and [Other] with your actual column names in the formula.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Thanks for you response 123abc

I probably didn't explain the situation adequately.

I have a column called [Classification] within that column each entry is classified as "Hazard", "Near Hit" or "Other".

The [Reported_Date] column has dates in the "dd/mm/yyyy" format. I need to group by the months and for each month I need to calculate the ratio.

Many thanks

[123abc · Accepted solution]

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.

[123abc]

Thank you for clarifying. In that case, you'll need to use the GROUPBY and SUMMARIZE functions in DAX to group your data by month and then apply the ratio calculations. Assuming you have a column named [Month] in your 'Incidents_Consolidated' table that represents the month extracted from the [Reported_Date], you can use the following DAX formula:

RatioColumn =
VAR TotalHazardNearHit = SUMX ( VALUES ( Incidents_Consolidated[Classification] ), [Hazard] + [Near Hit] )
VAR TotalOther = SUMX ( VALUES ( Incidents_Consolidated[Classification] ), [Other] )
RETURN
IF (
TotalHazardNearHit + TotalOther = 0,
"0:0",
IF (
TotalHazardNearHit = 0 && TotalOther > 0,
"0:1",
IF (
TotalHazardNearHit = TotalOther,
"1:0",
ROUND ( DIVIDE ( TotalHazardNearHit, TotalOther ), 1 ) & ":1"
)
)
)

This formula aggregates the sum of [Hazard] + [Near Hit] and [Other] for each group (month) using the SUMX function along with the VALUES function. Then, it applies the ratio calculations based on your conditions. The result is a ratio for each month.

Make sure to replace [Month], [Hazard], [Near Hit], and [Other] with the actual column names in your 'Incidents_Consolidated' table.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.