Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 number of ABMO/BAME staff members divided by the Total Employees in a given period.
In Power Query I have a column which has all staff's Ethnic Origin in it and I have created a duplicate column that simplifies this further so that the only 3 values are ABMO, White or Prefer Not to Say. I have a NonFTE Headcount figure already on another table, so what I want to do inititally is get a total number of ABMO values that occur in the table in PQ and then following that do a calculation to divide that figure by the Total Employees in the business leaving me with a final % figure to feed in the KPIs.
I am sure there is a really simple solution to this but my sleep deprived brain is not helping me to solve the problem.
All help appreciated and let me know if you need me to provide any further information.
Thanks
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:
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.