Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I’m hoping someone can help me with the following, I’m trying to create turnover rate, but I’ve had no success.
I have 3 tables:
Staff Summary, Leaver Summary and Transfer to Casuals all of which joined by the shared field KEY.
I’ve created a summary table called Total using the following code:
Totals = SUMMARIZE('Staff Summary','Staff Summary'[Year],'Staff Summary'[Level4:Structure],'Staff Summary'[Staff Type],"Staff FTE",sum('Staff Summary'[Staff FTE]),"Leaver FTE",Calculate(sum('Leavers Summary'[Leaver FTE]),ALLEXCEPT('Leavers Summary','Leavers Summary'[KEY])),"Transfer FTE",CALCULATE(sum('Transfer to Casuals'[Transfer FTE]),ALLEXCEPT('Transfer to Casuals','Transfer to Casuals'[KEY])))
This looks fine.
Apart from Turnover which is a calculated measure
Turnover Rate = DIVIDE((Sum('Leavers Summary'[Leaver FTE])+SUM('Transfer to Casuals'[Transfer FTE])),SUM('Staff Summary'[Staff FTE]))
It seems to be the total rate applied to all rows.
I've attached the screen shot below.
Any help would be greatfully apreciated.
Thanks
Hi @Anonymous ,
Did you create the calculated table totals with your fact table Staff Summary? Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.
Regards,
Frank
Thanks for your reply, much appreciated.
Staff Summary is a Summary table based on 3 fact tables:
Report(5)
Staff Summary = SUMMARIZE('Report 1 (5)','Report 1 (5)'[Year],'Report 1 (5)'[Level4:Structure],'Report 1 (5)'[Staff Type],"Staff FTE",SUM('Report 1 (5)'[FTE - Staff]))
Casuals(3)
Transfer to Casuals = Summarize('Casuals (2)','Casuals (2)'[Year],'Casuals (2)'[Level4:Structure],'Casuals (2)'[Staff Type],"Tranfer FTE",SUM('Casuals (2)'[FTE]))
Leavers(2).
Leavers Summary = Summarize('Leavers (3)','Leavers (3)'[Year],'Leavers (3)'[Level4:Structure],'Leavers (3)'[Staff Type],"Leaver FTE",Sum('Leavers (3)'[FTE - Leavers]))
Finaly Followed by the creation of Staff Summary which is referenced for Totals
Staff Summary = SUMMARIZE('Report 1 (5)','Report 1 (5)'[Year],'Report 1 (5)'[Level4:Structure],'Report 1 (5)'[Staff Type],"Staff FTE",SUM('Report 1 (5)'[FTE - Staff]))
For Turnover Rate I'm seeking for each row Total Leavers / Staff FTE i.e. for 2018:
2018 29.37%(146.36/498.35)
Dev: 38.29%(128.76/336.27)
Office: 10.86%(17.6/162.08)
Hope this clarifies things?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |