Forum Discussion
Ethnicity Workforce Gap
No worries at all! Calculating the Ethnicity Workforce Gap in Power BI is a common requirement, and I'm here to help you with that.
Let's break down the steps to achieve this in Power BI using Power Query and DAX:
1. **Count ABMO Values in Power Query (M Language)**:
In Power Query Editor, you can use the "Group By" feature to count the occurrences of ABMO values in your Ethnic Origin column. Here's how you can do it:
- Go to the Power Query Editor.
- Select your table.
- Click on the "Group By" option in the Home tab.
- Group by your Ethnic Origin column and choose "Count Rows" as the operation.
- Filter the resulting table to keep only the row where Ethnic Origin is "ABMO".
- Rename the column to something meaningful (e.g., "ABMO Count").
2. **Calculate Total Employees in DAX**:
In your Power BI report, you'll use DAX to calculate the total number of employees. If you have a table with NonFTE Headcount, you can simply sum this column to get the total. If you don't have such a table, you can count the rows in your main table that represent employees. Here's an example DAX measure:
```
Total Employees = COUNTROWS('YourMainTable')
```
3. **Calculate Ethnicity Workforce Gap in DAX**:
Finally, you'll create a DAX measure to calculate the Ethnicity Workforce Gap. You'll divide the count of ABMO values by the total number of employees and express it as a percentage. Here's how:
```
Ethnicity Workforce Gap (%) =
DIVIDE(
[ABMO Count],
[Total Employees],
0
) * 100
```
Make sure to replace `[ABMO Count]` with the name of your measure or column that contains the count of ABMO values, and `[Total Employees]` with the name of your measure that calculates the total number of employees.
Once you have these measures created, you can add the Ethnicity Workforce Gap (%) measure to your report and visualize it as needed.
Feel free to ask if you have any further questions or need clarification on any step!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Hi. Thank you for coming back to me on my problem. I understand the above solution but I still come up with an issue when implementing it. i've followed your step by step guidance but am still coming up against a few blockers to get a final calculation done, i'll try and set them out below:
- Our main "Person/Employee" table has every single employee past and present in there along with duplicated rows for multiple employees, they have the same employee number, but a different person key, but this leads to duplicate counts of the ABMO ethnic grouping. Do I need to filter this list down so that there is only 1 line per employee to allow this to work. When doing other work on the report I am able to add filters to visuals to only include the individual once, but working in data query on this particular problem seems to caused duplicates to be counted.
- Should I be creating a duplicate of the Person/Employee table before doing the grouping as when I do group it turns the table into a two column table. Once thats duplicated, the proceed with the grouping and use that new table for the grouped data?
- The final measure you proposed doesn't seem to want to work when I try and create it, when I try and create it I can't input the ABMO Count as the numerator as it doesn't appear when I start to type out th the column name
- This is the grouped up view before filtering to only leave ABMO, the true count of ABMO is 95 out of a possible 793, White 632 and Not provided 66.
Sorry for all the additional questions, hopefully they make sense, please let me know if you need further information about our database.
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.Deduplicate the Person/Employee Table: Use the "Remove Duplicates" transformation in Power Query to ensure each employee is represented only once in the table.
Group the Data to Count ABMO Values: After deduplication, group the data by the Ethnic Origin column and count the occurrences of "ABMO".
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.
Create the Final DAX Measure: Use the measures created in step 3 to calculate the Ethnicity Workforce Gap as per the initial solution provided.
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.
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
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
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?
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.
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.