cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gyorgykovacs Regular Visitor
Regular Visitor

How to measure female representation within total headcount by month?

Hi Everyone,

 

I have a dataset called 'Worker extract' with the fields called

  • Employee ID (unique IDs by employee)
  • Market ('Continental Europe', 'Africa Regional Markets', 'Colombia'...etc.)
  • Gender ('Female', 'Male')
  • Month ('F17 P01'; 'F17 P02'... 'F18 P01' to diffenciate the different months of reporting, so one employee has as much records within the dataset as much months of service he or she has)

My questions are:

 

1) How do I create a matrix that shows only the female representation (%, 1 decimal) of the total headcount by Market and which I can filter by Month and any other possible variable that I add as a filter?

 

2) How can I add a column to the matrix above which shows the difference between the measure and 6 months prior's data? (So if I selected "F17 P07" on my filter I would see F17 P07 female % on the first column and the difference between F17 P07 and F17 P01 numbers?

 

Thank you very much for all your help in advance, it is very much appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to measure female representation within total headcount by month?

@gyorgykovacs,

 

You may refer to the following steps.

1) add a calculated column

Date =
DATE ( VALUE ( "20" & MID ( 'Worker extract'[Month], 2, 2 ) ), VALUE ( RIGHT ( 'Worker extract'[Month], 2 ) ), 1 )

2) add measures

Current =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Worker extract'[Employee ID] ),
        'Worker extract'[Gender] = "Female"
    ),
    DISTINCTCOUNT ( 'Worker extract'[Employee ID] )
)
6 months prior =
VAR d =
    MAX ( 'Worker extract'[Date] )
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( 'Worker extract'[Employee ID] ),
            ALL ( 'Worker extract'[Month] ),
            'Worker extract'[Gender] = "Female",
            'Worker extract'[Date]
                = DATE ( YEAR ( d ), MONTH ( d ) - 6, 1 )
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Worker extract'[Employee ID] ),
            ALL ( 'Worker extract'[Month] ),
            'Worker extract'[Date]
                = DATE ( YEAR ( d ), MONTH ( d ) - 6, 1 )
        )
    )
Difference =
[Current] - [6 months prior]
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Community Support Team
Community Support Team

Re: How to measure female representation within total headcount by month?

@gyorgykovacs,

 

You may refer to the following steps.

1) add a calculated column

Date =
DATE ( VALUE ( "20" & MID ( 'Worker extract'[Month], 2, 2 ) ), VALUE ( RIGHT ( 'Worker extract'[Month], 2 ) ), 1 )

2) add measures

Current =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'Worker extract'[Employee ID] ),
        'Worker extract'[Gender] = "Female"
    ),
    DISTINCTCOUNT ( 'Worker extract'[Employee ID] )
)
6 months prior =
VAR d =
    MAX ( 'Worker extract'[Date] )
RETURN
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( 'Worker extract'[Employee ID] ),
            ALL ( 'Worker extract'[Month] ),
            'Worker extract'[Gender] = "Female",
            'Worker extract'[Date]
                = DATE ( YEAR ( d ), MONTH ( d ) - 6, 1 )
        ),
        CALCULATE (
            DISTINCTCOUNT ( 'Worker extract'[Employee ID] ),
            ALL ( 'Worker extract'[Month] ),
            'Worker extract'[Date]
                = DATE ( YEAR ( d ), MONTH ( d ) - 6, 1 )
        )
    )
Difference =
[Current] - [6 months prior]
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gyorgykovacs Regular Visitor
Regular Visitor

Re: How to measure female representation within total headcount by month?

That is fantastic, thank you very much, works perfectly.

Could you please explain me why is it needed to add the calculated column 'Date' in point 1) ?

Community Support Team
Community Support Team

Re: How to measure female representation within total headcount by month?

@gyorgykovacs,

 

Given the sample data, that makes date calculation easy.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gyorgykovacs Regular Visitor
Regular Visitor

Re: How to measure female representation within total headcount by month?

It is a custom date column, since

 

F17 P01 = 1st to 31st July 2016

F17 P02 = 1st to 31st August 2016

...

F18 P12 = 1st to 31st of June 2019

 

Is the calculation the same or does this information changes the whole model?

 

(I actually have another dim table in my dateset to make this conversion for my other fact tables.)

Community Support Team
Community Support Team

Re: How to measure female representation within total headcount by month?

@gyorgykovacs,

 

No problem with that. But learn more about DAX before moving forward.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gyorgykovacs Regular Visitor
Regular Visitor

Re: How to measure female representation within total headcount by month?

I am planning to do that! Smiley Happy

Btw, one more question if you don't mind. Does this formula shows the markets with 0% of female rates?

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 271 members 2,842 guests
Please welcome our newest community members: