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 have created a table the following table using the summarise function - The FTE's are created using the sum function.
UNIT Staff FTE Leaver FTE Transfer FTE Total Leavers Turnover Rate
A 500.7 102 5 107
B 120 20 2 22
I created a new measure to calculate the Total Leavers (i.e. Leaver FTE + Transfer FTE). when I try to create the turnover rate (i.e. Total Leavers/Staff FTE) I get non sensical results using the divide functon.
Can anyone help please?
Thanks
@Anonymous ,
You may check measure below.
Measure = DIVIDE ( SUM ( Table1[Leaver FTE] ) + SUM ( Table1[Transfer FTE] ), SUM ( Table1[Staff FTE] ) )
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])))
Hi,
I'm afraid that's not working. I get the total rate applied to each category when I apply that formula.
To add a bit more to my original post. I have 3 summary tables(Leaver Summary, Staff Summary and Transfer Summary) which I have joined by a key.
Hi,
I've tried to build the summary table again, but this time calculate all measures with the SUMMARIZE fuction.
Totals2 = 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]) ), "Total Leavers",CALCULATE ( sum('Transfer to Casuals'[Transfer FTE]), ALLEXCEPT('Transfer to Casuals','Transfer to Casuals'[KEY]) ) + Calculate ( sum('Leavers Summary'[Leaver FTE]), ALLEXCEPT('Leavers Summary','Leavers Summary'[KEY]) ), "Turnover",( DIVIDE( CALCULATE ( sum('Transfer to Casuals'[Transfer FTE]), ALLEXCEPT('Transfer to Casuals','Transfer to Casuals'[KEY]) ) + Calculate ( sum('Leavers Summary'[Leaver FTE]), ALLEXCEPT('Leavers Summary','Leavers Summary'[KEY]) ) , CALCULATE ( sum('Staff Summary'[Staff FTE]), ALLEXCEPT('Staff Summary','Staff Summary'[KEY]) ) ) ) )
again the result for turnover rate is not what I'm expecting.
anay help would be great.
Thanks
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 |