Forum Discussion

pparke's avatar
pparke
Frequent Visitor
2 years ago
Solved

Interpret measures

Hi, 

 

New to Power BI and trying to interpret measures that have been created by someone else. Could anyone intrepret these please? 

 

1.

Rolling HC =
VAR MaxIndex = MAX(TURNOVER_Payroll_Calendar[Index])
VAR MinIndex = MaxIndex - 12
VAR Result =
CALCULATE(
    (COUNT('FINAL_TURNOVER_MERGED'[Position ID])/12), FILTER(ALLEXCEPT('FINAL_TURNOVER_MERGED','FINAL_TURNOVER_MAPPING'[Org Unit],'FINAL_TURNOVER_MAPPING'[NDIS Roles],'FINAL_TURNOVER_MAPPING'[Service Area], 'FINAL_TURNOVER_MAPPING'[Location],'MAPPING_OVERVIEW'[Location],'FINAL_TURNOVER_MAPPING'[Position ID]),'FINAL_TURNOVER_MERGED'[Index] <= MaxIndex && 'FINAL_TURNOVER_MERGED'[Index] > MinIndex && 'FINAL_TURNOVER_MERGED'[Source] = "Position"
    )
   )
RETURN
IF(ISBLANK(Result),0,
Result)
 
 
2. 
Rolling Term =
VAR MaxIndex = MAX(TURNOVER_Payroll_Calendar[Index])
VAR MinIndex = MaxIndex - 12
VAR Result =
CALCULATE(
    (COUNT('FINAL_TURNOVER_MERGED'[Position ID])), FILTER(ALLEXCEPT('FINAL_TURNOVER_MERGED','FINAL_TURNOVER_MAPPING'[Org Unit],'FINAL_TURNOVER_MAPPING'[NDIS Roles],'FINAL_TURNOVER_MAPPING'[Service Area], 'FINAL_TURNOVER_MAPPING'[Location],'MAPPING_OVERVIEW'[Location],'FINAL_TURNOVER_MAPPING'[Position ID]),'FINAL_TURNOVER_MERGED'[Index] <= MaxIndex && 'FINAL_TURNOVER_MERGED'[Index] > MinIndex && 'FINAL_TURNOVER_MERGED'[Source] = "Offboard"
    )
   )
RETURN
IF(ISBLANK(Result),0,
Result)
 
 
Thank You!  
Need Help
  • BA_Pete's avatar
    BA_Pete
    2 years ago

    Hi pparke ,

     

    This is actually a use-case for which tools like ChatGPT can be your friend. I've reviewed the responses to ensure they haven't been hallucinated.

    ** The following responses were generated by ChatGPT 4o  **

     

    Measure 1:

    This DAX measure, `Rolling HC`, calculates the rolling average headcount over a 12-month period for specific organizational units, roles, service areas, locations, and positions within a dataset. Here’s a breakdown of what each part does:

    1. **Variable Definitions**:
    - `MaxIndex`: Finds the maximum value of the `Index` column in the `TURNOVER_Payroll_Calendar` table within the current filter context.
    - `MinIndex`: Calculates the minimum index for the 12-month rolling window by subtracting 12 from `MaxIndex`.

    2. **Result Calculation**:
    - The `CALCULATE` function is used to modify the context in which the data is evaluated.
    - `(COUNT('FINAL_TURNOVER_MERGED'[Position ID])/12)`: Counts the number of unique `Position ID` values in the `FINAL_TURNOVER_MERGED` table and divides by 12 to get a monthly average.
    - `FILTER(ALLEXCEPT(...))`: Removes any filters from the `FINAL_TURNOVER_MERGED` table except those on specific columns related to organizational structure (`Org Unit`, `NDIS Roles`, `Service Area`, `Location`, and `Position ID`).
    - The `FILTER` function restricts the data to rows where the `Index` is within the 12-month window (`<= MaxIndex && > MinIndex`) and the `Source` column is equal to "Position".

    3. **Return Value**:
    - The `IF` function checks if `Result` is blank.
    - If `Result` is blank, it returns 0.
    - Otherwise, it returns the calculated `Result`.

    In summary, this measure calculates the average monthly headcount over the past 12 months for specified organizational dimensions. If no data is found, it returns 0.

     

    Measure 2:

    This DAX measure, `Rolling Term`, calculates the rolling total number of terminations (offboarding) over a 12-month period for specific organizational units, roles, service areas, locations, and positions within a dataset. Here's a detailed breakdown:

    1. **Variable Definitions**:
    - `MaxIndex`: Finds the maximum value of the `Index` column in the `TURNOVER_Payroll_Calendar` table within the current filter context.
    - `MinIndex`: Calculates the minimum index for the 12-month rolling window by subtracting 12 from `MaxIndex`.

    2. **Result Calculation**:
    - The `CALCULATE` function is used to modify the context in which the data is evaluated.
    - `(COUNT('FINAL_TURNOVER_MERGED'[Position ID]))`: Counts the number of unique `Position ID` values in the `FINAL_TURNOVER_MERGED` table.
    - `FILTER(ALLEXCEPT(...))`: Removes any filters from the `FINAL_TURNOVER_MERGED` table except those on specific columns related to organizational structure (`Org Unit`, `NDIS Roles`, `Service Area`, `Location`, and `Position ID`).
    - The `FILTER` function restricts the data to rows where the `Index` is within the 12-month window (`<= MaxIndex && > MinIndex`) and the `Source` column is equal to "Offboard".

    3. **Return Value**:
    - The `IF` function checks if `Result` is blank.
    - If `Result` is blank, it returns 0.
    - Otherwise, it returns the calculated `Result`.

    In summary, this measure calculates the total number of terminations (offboardings) over the past 12 months for specified organizational dimensions. If no data is found, it returns 0.

     

    Pete

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    Hi pparke ,

     

    This is actually a use-case for which tools like ChatGPT can be your friend. I've reviewed the responses to ensure they haven't been hallucinated.

    ** The following responses were generated by ChatGPT 4o  **

     

    Measure 1:

    This DAX measure, `Rolling HC`, calculates the rolling average headcount over a 12-month period for specific organizational units, roles, service areas, locations, and positions within a dataset. Here’s a breakdown of what each part does:

    1. **Variable Definitions**:
    - `MaxIndex`: Finds the maximum value of the `Index` column in the `TURNOVER_Payroll_Calendar` table within the current filter context.
    - `MinIndex`: Calculates the minimum index for the 12-month rolling window by subtracting 12 from `MaxIndex`.

    2. **Result Calculation**:
    - The `CALCULATE` function is used to modify the context in which the data is evaluated.
    - `(COUNT('FINAL_TURNOVER_MERGED'[Position ID])/12)`: Counts the number of unique `Position ID` values in the `FINAL_TURNOVER_MERGED` table and divides by 12 to get a monthly average.
    - `FILTER(ALLEXCEPT(...))`: Removes any filters from the `FINAL_TURNOVER_MERGED` table except those on specific columns related to organizational structure (`Org Unit`, `NDIS Roles`, `Service Area`, `Location`, and `Position ID`).
    - The `FILTER` function restricts the data to rows where the `Index` is within the 12-month window (`<= MaxIndex && > MinIndex`) and the `Source` column is equal to "Position".

    3. **Return Value**:
    - The `IF` function checks if `Result` is blank.
    - If `Result` is blank, it returns 0.
    - Otherwise, it returns the calculated `Result`.

    In summary, this measure calculates the average monthly headcount over the past 12 months for specified organizational dimensions. If no data is found, it returns 0.

     

    Measure 2:

    This DAX measure, `Rolling Term`, calculates the rolling total number of terminations (offboarding) over a 12-month period for specific organizational units, roles, service areas, locations, and positions within a dataset. Here's a detailed breakdown:

    1. **Variable Definitions**:
    - `MaxIndex`: Finds the maximum value of the `Index` column in the `TURNOVER_Payroll_Calendar` table within the current filter context.
    - `MinIndex`: Calculates the minimum index for the 12-month rolling window by subtracting 12 from `MaxIndex`.

    2. **Result Calculation**:
    - The `CALCULATE` function is used to modify the context in which the data is evaluated.
    - `(COUNT('FINAL_TURNOVER_MERGED'[Position ID]))`: Counts the number of unique `Position ID` values in the `FINAL_TURNOVER_MERGED` table.
    - `FILTER(ALLEXCEPT(...))`: Removes any filters from the `FINAL_TURNOVER_MERGED` table except those on specific columns related to organizational structure (`Org Unit`, `NDIS Roles`, `Service Area`, `Location`, and `Position ID`).
    - The `FILTER` function restricts the data to rows where the `Index` is within the 12-month window (`<= MaxIndex && > MinIndex`) and the `Source` column is equal to "Offboard".

    3. **Return Value**:
    - The `IF` function checks if `Result` is blank.
    - If `Result` is blank, it returns 0.
    - Otherwise, it returns the calculated `Result`.

    In summary, this measure calculates the total number of terminations (offboardings) over the past 12 months for specified organizational dimensions. If no data is found, it returns 0.

     

    Pete