Forum Discussion
Ethnicity Workforce Gap
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.