Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
I have a dataset called 'Worker extract' with the fields called
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!
Solved! Go to Solution.
@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]
@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]
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.
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.
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |