cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FalkirkFC Frequent Visitor
Frequent Visitor

Creating a turnover percentage

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

3 REPLIES 3
Community Support Team
Community Support Team

Re: Creating a turnover percentage

@FalkirkFC ,

 

You may check measure below.

Measure =
DIVIDE (
    SUM ( Table1[Leaver FTE] ) + SUM ( Table1[Transfer FTE] ),
    SUM ( Table1[Staff FTE] )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
FalkirkFC Frequent Visitor
Frequent Visitor

Re: Creating a turnover percentage

Totals = SUMMARIZE('Staff Summary','Staff Summary'[Year],'Staff Summary'[Level4Smiley Frustratedtructure],'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])))

Turnover Rate.PNG

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.

FalkirkFC Frequent Visitor
Frequent Visitor

Re: Creating a turnover percentage

 

 

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'[Level4Smiley Frustratedtructure],
                   '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.

 

Turnover Rate2.PNG

 

anay help would be great.

 

Thanks