Using Count Distinct and Sum

[#1]

Hi,

I have tried to understand the answers for this question that have been posted previously but I am having trouble applying it.

Here is my dilemma. I would like to count distinct the engineers on a project and the sum the hours they have worked divided by 2080 (FTE). This would show up as a KPI. Basically a Productivity Matrix.

Engineers      Actual Hours

John                100

James             600

mike                6

john                 400

James             100

This is what I would like to get when I filter by engineer.

Total Engineers = 100%

John  = 24%

James = 33.6%

this is what I have used - sum(aggr(sum(DISTINCT [Engineer]),[Actual Hours]))

Thanks

[el_aprendiz111]

Hi

sum([Actual Hours])/2080

[Anonymous]

sounds like you really want

sum([Actual Hours])/sum(TOTAL [Actual Hours])

[ahaahaaha]

Hi,

If I understand you correctly, it can be as in an attached file.

Regards,

Andrey

[vinieme12]

just incase , you might want to ignore filters on the denominator add {1}

sum([Actual Hours])/sum( {1} TOTAL [Actual Hours])

[#6]

Thanks for the help, but this is what I am looking for, (My script writing it not the best)

sum(Count(distinct[Engineers],[Actual Hours])/2080

Basically:

John total hours a year he can work is 2080

He only worked 500 but they are spread out in 2 instances i.e. 100 and 400

How do I count one instance of John and sum the number of hours he worked.

Thanks again.

[Anonymous]

How about this?

[NickHoff]

You were close with your first attempt, but try

aggr(sum([Actual Hours])/2080,Engineers)

[#9]

Hi Nick,

Thanks for the help. This Worked!!

Thanks also to everyone to jumped in.