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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cynthiabucher
Helper I
Helper I

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 @cynthiabucher ,

 

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

@cynthiabucher 

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

cynthiabucher
Helper I
Helper I

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

@cynthiabucher 

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

cynthiabucher
Helper I
Helper I

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

@cynthiabucher 

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

cynthiabucher
Helper I
Helper I

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

@cynthiabucher 


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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