Employee Watefall

[Tahar]

Hello,
I want to create a Watefall that displays the number of employees by reason of entry / leaving
, by year and by country according to the start date and end date.

here is my result.

the problem is that i can’t find the right figures.

I have a Data model that contains 3 types of tables. A fact table “t_contract”, assignment tables which contains the start date & end date “h_con …” and dimension tables “r_ …”

my relationships are:

t_contract (con_id) 1: * h_con_rfe (fk_con_id)
h_con_rfe (rfe_id) * : 1 r_ReasonForEntry (fkid_rfe)
Dates (Date) 1:* h_con_rfe (sdate)
t_contract (con_id) 1 : * h_con_rfl (fk_con_id)
h_con_rfl (rfe_id) :1 r_ReasonForLeaving (fkid_rfl)
Dates (Date) 1: h_con_rfl (edate)
t_contract (con_id) 1: * h_con_cou (fk_con_id)
h_con_cou (fk_con_id) * : 1 r_countries (fkid_cou)

Here is an example of the content of the tables

I created a table that groups the rfe / rfl which is not connected to any tabel.

I then created this list of Measures.

RFE Contract = COUNTROWS(t_contracts)

RFL Contract = CALCULATE(COUNTROWS(t_contracts);USERELATIONSHIP(‘Dates (1)’[Date];h_con_rfl[hcrl_eDate]))

HIRE = CALCULATE([RFE Contract];FILTER(r_reasonsForEntry;r_reasonsForEntry[rfe_id]=5))

RESIGN = CALCULATE(-[RFL Contract]; FILTER (r_reasonsForLeaving;r_reasonsForLeaving[rfl_id]=12))

Current Contracts Selection =
SWITCH( TRUE();
VALUES( ‘Reason_EL’[Reason_EL] ) = “ATL_END”; [ATL_END];
VALUES( ‘Reason_EL’[Reason_EL] ) = “DEATH”; [DEATH];
VALUES( ‘Reason_EL’[Reason_EL] ) = “EDF_MOB_L”; [EDF_MOB];
VALUES( ‘Reason_EL’[Reason_EL] ) = “END_TRIAL”; [END_TRIAL];
VALUES( ‘Reason_EL’[Reason_EL] ) = “FTC_END”; [FTC_END];
VALUES( ‘Reason_EL’[Reason_EL] ) = “MOB_L”; [MOB_L];
VALUES( ‘Reason_EL’[Reason_EL] ) = “MUT_AGREE”; [MUT_AGREE];
VALUES( ‘Reason_EL’[Reason_EL] ) = “OTH_L”; [OTH_L];
VALUES( ‘Reason_EL’[Reason_EL] ) = “REDUND”; [REDUND];
VALUES( ‘Reason_EL’[Reason_EL] ) = “RESIGN”; [RESIGN];
VALUES( ‘Reason_EL’[Reason_EL] ) = “RET”; [RET];
VALUES( ‘Reason_EL’[Reason_EL] ) = “RIF_PDV”; [RIF_PDV];
VALUES( ‘Reason_EL’[Reason_EL] ) = “ATL->NFT”; [ATL->NFT];
VALUES( ‘Reason_EL’[Reason_EL] ) = “EDF_MOB_E”; [EDF_MOB_E];
VALUES( ‘Reason_EL’[Reason_EL] ) = “FTC_RENEW”; [FTC_RENEW];
VALUES( ‘Reason_EL’[Reason_EL] ) = “FTC_NFT”; [FTC_NFT];
VALUES( ‘Reason_EL’[Reason_EL] ) = “HIRE”; [HIRE];
VALUES( ‘Reason_EL’[Reason_EL] ) = “MOB_E”; [MOB_E];
VALUES( ‘Reason_EL’[Reason_EL] ) = “OTH_E”; [OTH_E];
VALUES( ‘Reason_EL’[Reason_EL] ) = “SEC_IN”; [SEC_IN];
0)

To create the Waterfall I put Reason_EL in categories and Current Contracts Selection in Axis Y
I can’t find the right numbers and I don’t know where I got it wrong. Can anyone help me.

Thank you

[sam.mckay]

Really appreciate all the detailed information, but just by going off what you’ve given it’s very very difficult to truly have an understanding of what’s going on.

There’s just so much that it could be. It could be something with your model, it could be something with your data, could be something with your formula but it’s hard to test out these things without actually seeing and playing around with the model.

The first thing I know for sure is that your model is certainly not optimised for how it should look in power BI. Have you gone through the advanced transformations in modelling course?

I highly highly recommend this because that will teach you all the best practice tips on how to build your model and an optimised way for Power BI.

Have you also gone through some of the HR and employee showcases and content that is already provided in the online portal?

I highly recommend trying to work through these all to solve some of these things. I’m confident that when you get a really solid background understanding of your data model and also some of the techniques to calculate employee type metrics , You’ll be able to solve this pretty quickly.

[sam.mckay]

For additional help on this I would recommend just setting up a demo model with only the darter that is relevant to the specific question. That would enable someone to have a look at your model and quickly work it out

[EnterpriseDNA]

Hi @Tahar, we’ve noticed that no response has been received from you since the 4th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!