Absenteeism data issue with the sum of columns based on multiple time types

[amd567]

I am trying to calculate the average FTE per department (Meaningful level). 

The issue I am running into is that I have multiple rows per month for the different Absence Time Types against one FTE value for the month.

The avaerage FTE calculation is: RY:Absence Day Total / FTE RY:Final. 

To bring the FTE RY: Final value into all rows I am filtering FTE RY:Final based on the department and Reporting Month. 

I am getting the correct value for Average FTE per Row but it is then totaling this at the bottom instead of dividing the total values. 

So I am looking for the Average FTE to total 2.53 (65.00/25.70) instead of suming all the rows together. 

I am assuming I need to use a filter in my calculation but I cant seem tog et the right combination.

[v-henryk-mstf · Accepted solution]

Hi @amd567 ,

You can try below formula:

M = 
VAR a =
    SELECTEDVALUE ( 'Table'[Time Type (groups)] )
RETURN
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Time Type (groups)] = a ),
        [Absences/FTE People 2]
    )

If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[vojtechsima]

Hi, @amd567 
here's how you can manipulate the total:

Measure = 
var SegmentCount = COUNTROWS('Average')
var calc_ = CALCULATE( DIVIDE( SUM('Average'[ForAverage]), SegmentCount ))

var no_filter = SUM('Average'[ForAverage])
var last_check  = IF(ISFILTERED('Average'), calc_, no_filter) 

return last_check

For First measure I am returning the "calc_" variable, so naturally if I write the measure like this, I get average even in the total (you can look at that and modify your measure). In the second table I put "last_check" as the return value and it sums up the values if there's no filter.

[amd567]

Hi @vojtechsima thanks for your reply. 

I have tried to create a measure from your example but it still doesnt seem to be giving me the desired result. 

Measure New = 
var SegmentCount = COUNTROWS('Table')
var calc_ = CALCULATE(DIVIDE( SUM('Table'[Average FTE]), SegmentCount ))

var no_filter = SUM('Table'[Average FTE])
var last_check  = IF(ISFILTERED('Table'), calc_, no_filter) 

return calc_

It is giving me the correct row level values which I had been able to calculate before but the total value is 0.63. 

I would like the total value to show the 2.53. Which is the RY:Absence Day Total / FTE RY: Final. 

I think my issue is with the FTE per meaningful level column. This is adding the FTE value together for each Time Type rather than just adding the one value for the month. I.e Feb 2021 should equal juat 12.93 but it is adding this 4 times. 

This is the column calculation I am using to be able to get the row level FTE per meaningful level breakdown.

FTE per meaningful level & Month = CALCULATE(

SUM('Table'[FTE RY: Final]),

FILTER('Table', 'Table'[Business Group 1] = EARLIER('Table'[Business Group 1] )

&& 'Table'[Business Group 2] = EARLIER('Table'[Business Group 2])

&& 'Table'[Meaningful Level] = EARLIER('Table'[Meaningful Level])

&& 'Table'[Reporting Month] = EARLIER('Table'[Reporting Month] ))

)

[vojtechsima]

Hi,
@amd567 
Any chance you could send a copyable dataset of your data? You can send just some random numbers but keep the structure so I can play with it. Thank you

[amd567]

Hi @vojtechsima 

I cant attach a file so hopefully you will be able to copy the below sample. 

Business Group 2	Meaningful Level	Reporting Month	Time Type	Time Type (groups)	RY : Absence Day Total	FTE RY: Final	FTE per meaningful level & Month
ES	ES Process Improvement	Feb-21	Sick Leave-Cert.	Cert	26.5	0	12.933
ES	ES Process Improvement	Feb-21	Not assigned	Not assigned	0	12.933	12.933
ES	ES Process Improvement	Feb-21	Casual Sick Leave	Casual	7	0	12.933
ES	ES Process Improvement	Feb-21	Cas. Sick Leave unpaid	Casual	1.5	0	12.933
ES	ES Process Improvement	Mar-21	Sick Leave-Cert.	Cert	23.5	0	12.763
ES	ES Process Improvement	Mar-21	Not assigned	Not assigned	0	12.763	12.763
ES	ES Process Improvement	Mar-21	Casual Sick Leave	Casual	5	0	12.763
ES	ES Process Improvement	Mar-21	Cas. Sick Leave unpaid	Casual	1.5	0	12.763
ES	ES Process Improvement	Apr-21	Sick Leave-Cert.	Cert	23.5	0	12.61
ES	ES Process Improvement	Apr-21	Not assigned	Not assigned	0	12.61	12.61
ES	ES Process Improvement	Apr-21	Casual Sick Leave	Casual	3	0	12.61
ES	ES Process Improvement	Apr-21	Cas. Sick Leave unpaid	Casual	1.5	0	12.61
ES	ES Procurement	Feb-21	Sick Leave-Cert.	Cert	245.5	0	57.465
ES	ES Procurement	Feb-21	Not assigned	Not assigned	0	57.465	57.465
ES	ES Procurement	Feb-21	Casual Sick Leave	Casual	32.5	0	57.465
ES	ES Procurement	Mar-21	Sick Leave-Cert.	Cert	203.5	0	56.129
ES	ES Procurement	Mar-21	Not assigned	Not assigned	0	56.129	56.129
ES	ES Procurement	Mar-21	Casual Sick Leave	Casual	22.5	0	56.129
ES	ES Procurement	Mar-21	Cas. Sick Leave unpaid	Casual	1	0	56.129
ES	ES Procurement	Apr-21	Sick Leave-Cert.	Cert	209.5	0	56.848
ES	ES Procurement	Apr-21	Not assigned	Not assigned	0	56.848	56.848
ES	ES Procurement	Apr-21	Casual Sick Leave	Casual	18.5	0	56.848
ES	ES Procurement	Apr-21	Cas. Sick Leave unpaid	Casual	1	0	56.848

[vojtechsima]

Hi, @amd567 
Thanks for the data, this should fix it:

Absences/FTE People 2 = 

var RY_AbsenceDayTotal =  SUM('Table'[RY : Absence Day Total])
var AverageForRows = SUMX('Table', DIVIDE('Table'[RY : Absence Day Total], 'Table'[FTE per meaningful level & Month]))
var NoFilterResutlt = CALCULATE( DIVIDE(RY_AbsenceDayTotal,  SUM('Table'[FTE RY: Final])) )
var check = IF(HASONEFILTER('Table'[Reporting Month]), AverageForRows, NoFilterResutlt)
return  check

[amd567]

Hi @vojtechsima 

Thansk you so much for that I have that working now! 

However, my only issue is that when i filter on the Time Type the total disappears

[vojtechsima]

Hi, @amd567 
that's just because of the design of your calculations. You're dividing by the sum of rows that display 0 and you can't divide by 0 that's why there's a blank value.

[amd567]

Hi @vojtechsima thanks for getting back. 

Measure New = 
var SegmentCount = COUNTROWS('Table')
var calc_ = CALCULATE( DIVIDE( SUM('Table'[Average FTE]), SegmentCount ))

var no_filter = SUM('Table'[Average FTE])
var last_check  = IF(ISFILTERED('Table'), calc_, no_filter) 

return calc_

I have put my values into your formula above but I am still not getting the desired output so maybe you can see where I am going wrong?

I have highlighted the new measure output in yellow, the row values are correct as I had calculated before but this is totaling to 0.63. I want it to total the overall avaerage which is RY:Absence Day Total / FTE RY:Final (65/25.70 = 2.53 highlighted in red)

These are my other calculations which might help, both are columns:

Average FTE = ('Table'[RY : Absence Day Total]) /('Table'[FTE per meaningful level & Month])

FTE per meaningful level & Month = CALCULATE(

SUM('Table'[FTE RY: Final]),

FILTER('Table', 'Table'[Business Group 1] = EARLIER('Table'[Business Group 1] )

&& 'Table'[Business Group 2] = EARLIER('Table'[Business Group 2])

&& 'Table'[Meaningful Level] = EARLIER('Table'[Meaningful Level])

&& 'Table'[Reporting Month] = EARLIER('Table'[Reporting Month] )))

I also tried this measures but as you can see in the table they also dont give the desired output. 

Absences/FTE People = CALCULATE(SUM('Table'[RY : Absence Day Total])/SUM('Table'[FTE RY: Final]))

Absences/FTE People 2 = (SUM('Table'[RY : Absence Day Total])/SUM('Table'[FTE per meaningful level & Month]))

I think my issue might be with the FTE per meaningful level & month column suming all the values instead of holding just the one value per month per time type. For example Feb 2021 for that department is 12.93 and March is 12.76 which should total 25.70 but its suming each value for the timetype which is giving 102.78.

Is there a way to calcualte this column which will ignore the time type?

[SpartaBI]

@amd567 
"The avaerage FTE calculation is: RY:Absence Day Total / FTE RY:Final. "
Is that a column?
If yes, than all you need is to replace it with the equivalent measure:
FTE Measure = SUM(Absence Day Total) / SUM(FTE RY:Final) 
and put this measure in the visual instead of the column

[amd567]

Hi @SpartaBI 

Thanks for your reply. 

I have tried this in two ways.  

Absences/FTE People = CALCULATE(SUM('Table'[RY : Absence Day Total])/SUM('Table'[FTE RY: Final])) 

 Gives me the desired output at the bottom of the table (2.53) but no data on the row level. 

Absences/FTE People 2 = (SUM('Table'[RY : Absence Day Total])/SUM('Table'[FTE per meaningful level & Month]))

Gives me the desired row level data but the incorrect total at the bottom of the table. 

Do you have any suggestons on how I might combine these measures to give me the desired result?