Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Employee diversity by date - expanded table?

Hello!

I have diversity data for my company in the below format. My table is called DEI Detail

 

Employee ID, Start Date, Term Date, Gender, Race

 

I need to be able to use this data much more dynamically to show how many of each type of race and gender were employed on ANY specific day, week, or month. 

 

I feel like I need to expand this somehow... I have 634 employees and there are 365 days in a year so that would give me over 200k rows. I don't want to use excel.

 

Surely there is an easier way? Do I need to create measures? Should I try and create a custom table? I've been experimenting but Im newer at this and would love advice before I waste too much time. Thank you!

8 REPLIES 8
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Is this problem solved?


If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.


If not, please let me know.

 


Best Regards,
Icey

AlB
Super User
Super User

@Anonymous 

To show male and female next to each other you can place Table1[Gender] on the legend of  chart

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Anonymous
Not applicable

Ah I see. The measure you put in really helped me understand. 

The only thing I'm running in to now is - I need to have count of male and count of female next to each other in the same visual.  I also have a table with benchmarks that I add to it. Haha! It works so well in our applicant data since each applicant is entered rather than being active for a specific date range... 

AlB
Super User
Super User

@Anonymous 

See in the attached file the steps I enumerated earlier. You'll have to add some things like more columns to the date table and you might want to modify the measure slightly depending on what exactly you want to count on each month.  But this will give you a pretty good general idea as to how to do it.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Anonymous
Not applicable

I appreciate your offer.

Note - not everyone has a termination date, because they still work here. As evidenced by the double comma's

 

Employee Unique Identifier,Fulltime Hire Date,Termination Date,Gender,Race - Ethnicity
1128650,08/20/18,06/24/20,M,Hispanic
914688,05/20/19,06/24/20,M,White
11248288,01/27/20,06/24/20,M,Hispanic

11243535,08/24/20,,M,Asian
8192101,10/26/09,,F,White
289433,01/01/14,,F,White
8135459,08/01/20,,F,Black
678699,10/15/18,,M,Hispanic

 

AlB
Super User
Super User

@Anonymous 

I will give you a hand if you show a sample of your data (in text-tabular format, not  screen cap, so that it can be copied)

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Anonymous
Not applicable

I'm sorry, I read a few posts but am not skilled enough to apply what I saw on those. I will keep hunting... 

AlB
Super User
Super User

@Anonymous 


I feel like I need to expand this somehow... I have 634 employees and there are 365 days in a year so that would give me over 200k rows. I don't want to use excel.

No need to do at. At all. Best to use a date table whose fields you can use as axis for the chart.  Then create a measure to calculate the number of people employed at the specified time. You can use slicers to dynamically look at what you need: race, gender...

Have a look in the forum. This has been solved many times.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.