Forum Discussion

Andy2410's avatar
Andy2410
Frequent Visitor
2 years ago

Ethnicity Workforce Gap

Hi. I am still learning Power BI on the job so forgive me if this question sounds simplistic.    As part of our workforce KPIs I need to calculate the Ethnicity Workforce Gap, which is the total nu...
Tutorial Requests
johnbasha33's avatar
johnbasha33
Super User
2 years ago

Andy2410 
When creating the final DAX measure for the Ethnicity Workforce Gap, you need to ensure that the [ABMO Count] measure or column is correctly referenced. If it's not appearing when you try to input it, it might be due to the naming or the context in which you're trying to create the measure.

  1. Deduplicate the Person/Employee Table: Use the "Remove Duplicates" transformation in Power Query to ensure each employee is represented only once in the table.

  2. Group the Data to Count ABMO Values: After deduplication, group the data by the Ethnic Origin column and count the occurrences of "ABMO".

  3. Create Measures for Total Employees and ABMO Count: In your data model, create measures for the total number of employees and the count of ABMO values. These measures should be based on the deduplicated data.

  4. Create the Final DAX Measure: Use the measures created in step 3 to calculate the Ethnicity Workforce Gap as per the initial solution provided.

  5. Ensure Correct Naming and Context: When creating the final DAX measure, ensure that you're referencing the correct measures or columns and that they are visible and properly named in your data model.

Andy2410's avatar
Andy2410
Frequent Visitor
2 years ago

  1. Deduplicate the Person/Employee Table: Use the "Remove Duplicates" transformation in Power Query to ensure each employee is represented only once in the table. Done

  2. Group the Data to Count ABMO Values: After deduplication, group the data by the Ethnic Origin column and count the occurrences of "ABMO". Done, leaves a table with one one row and two columns (one named Ethnic Origin and the other ABMO Count

  3. Create Measures for Total Employees and ABMO Count: In your data model, create measures for the total number of employees and the count of ABMO values. These measures should be based on the deduplicated data. Created a total employee count measure using the NonFTEHeadcount column and COUNTROWS. How do I create a measure for the ABMO Count?

  4. Create the Final DAX Measure: Use the measures created in step 3 to calculate the Ethnicity Workforce Gap as per the initial solution provided. Once I have point 3 clarified I should be able to do this step.

  5. Ensure Correct Naming and Context: When creating the final DAX measure, ensure that you're referencing the correct measures or columns and that they are visible and properly named in your data model.

Thanks again for taking the time to assist.