Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX Formula for Calculating Ratios

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...
  • 123abc's avatar
    123abc
    2 years ago

    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's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      123abc
      Icon for Community Champion rankCommunity Champion
      2 years ago

      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.

    • 123abc's avatar
      123abc
      Icon for Community Champion rankCommunity Champion
      2 years ago

      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.