Help with calculated measures in a table

Hi Anonymous,

To create calculated measures in Power BI for the years 2022 and 2023, you would need to:

1. Define a Year Filter: Use the year values (2022 and 2023) as filters in your DAX measures.
2. Aggregate Based on Categories: Separate the calculations for <2 years, 2-5 years, and >5 years for both "Enhanced Supervision" and "Standard Supervision."

Here’s how you can create calculated measures step by step:

Example DAX Measures for 2022 and 2023

Assuming you have a column named Year, SupervisionType (Enhanced or Standard), DurationCategory (<2 years, 2-5 years, >5 years), and Count:

Measure for <2 Years - Enhanced Supervision (2022)

Enhanced_2022_LessThan2Years = 
CALCULATE(
    SUM(Table[Count]),
    Table[Year] = 2022,
    Table[SupervisionType] = "Enhanced Supervision",
    Table[DurationCategory] = "<2 years"
)

Measure for 2-5 Years - Enhanced Supervision (2023)

Enhanced_2023_2to5Years = 
CALCULATE(
    SUM(Table[Count]),
    Table[Year] = 2023,
    Table[SupervisionType] = "Enhanced Supervision",
    Table[DurationCategory] = "2-5 years"
)

Measure for >5 Years - Standard Supervision (2022)

Standard_2022_GreaterThan5Years = 
CALCULATE(
    SUM(Table[Count]),
    Table[Year] = 2022,
    Table[SupervisionType] = "Standard Supervision",
    Table[DurationCategory] = ">5 years"
)

Dynamic Measure for Any Combination

If you want to dynamically calculate values based on slicers for Year, SupervisionType, and DurationCategory:

DynamicMeasure = 
CALCULATE(
    SUM(Table[Count]),
    ALLSELECTED(Table[Year]),
    ALLSELECTED(Table[SupervisionType]),
    ALLSELECTED(Table[DurationCategory])
)

Steps to Implement in Power BI:

1. Go to the "Modeling" tab in Power BI Desktop.
2. Select "New Measure" and copy-paste the relevant DAX formula.
3. Use the measures in a table or matrix visualization, with State as rows and Year/categories as columns.

If your data model or column names differ, share more details so I can tailor the measures to your data.

Please mark this as solution if it helps. Appreciate Kudos.