Creating a turnover percentage

[Anonymous]

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

[v-chuncz-msft]

@Anonymous ,

You may check measure below.

Measure =
DIVIDE (
    SUM ( Table1[Leaver FTE] ) + SUM ( Table1[Transfer FTE] ),
    SUM ( Table1[Staff FTE] )
)

[Anonymous]

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.

[Anonymous]

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