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

Problems creating a turnover measure in a summarised table

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'[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])))

 

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

3 REPLIES 3
FalkirkFC Frequent Visitor
Frequent Visitor

Re: Problems creating a turnover measure in a summarised table

Totals.PNG

Community Support Team
Community Support Team

Re: Problems creating a turnover measure in a summarised table

Hi @FalkirkFC ,

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
FalkirkFC Frequent Visitor
Frequent Visitor

Re: Problems creating a turnover measure in a summarised table

Thanks for your reply, much appreciated.

 

Staff Summary is a Summary table based on 3 fact tables:

 

Report(5)

 

Report1 (5).PNGReport1 (5) 2.PNG

Staff Summary = SUMMARIZE('Report 1 (5)','Report 1 (5)'[Year],'Report 1 (5)'[Level4Smiley Frustratedtructure],'Report 1 (5)'[Staff Type],"Staff FTE",SUM('Report 1 (5)'[FTE - Staff]))

 

Casuals(3) 

Casuals (2).PNGCasuals (2)2.PNG

Transfer to Casuals = Summarize('Casuals (2)','Casuals (2)'[Year],'Casuals (2)'[Level4Smiley Frustratedtructure],'Casuals (2)'[Staff Type],"Tranfer FTE",SUM('Casuals (2)'[FTE]))

 

 

Leavers(2).

Casuals (2).PNGCasuals (2)2.PNG
Leavers Summary = Summarize('Leavers (3)','Leavers (3)'[Year],'Leavers (3)'[Level4Smiley Frustratedtructure],'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)'[Level4Smiley Frustratedtructure],'Report 1 (5)'[Staff Type],"Staff FTE",SUM('Report 1 (5)'[FTE - Staff]))

Totals.PNG

 

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?