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
Anonymous
Not applicable

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
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

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.

View solution in original post

6 REPLIES 6
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

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.
Anonymous
Not applicable

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) ?

@Anonymous,

 

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.
Anonymous
Not applicable

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.)

@Anonymous,

 

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.
Anonymous
Not applicable

I am planning to do that! 🙂

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

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.