Problems creating a turnover measure in a summarised table

[Anonymous]

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

[v-frfei-msft]

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

[Anonymous]

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?

[Anonymous]