Forum Discussion

runemalo's avatar
runemalo
Frequent Visitor
8 years ago

Loop through rows with max value

Hi all,   I am trying to have a measure that is calculating the sum of FTE's like in the picture below. The problem is that each person can not be measured as more than one FTE, but can be less. Th...
Need Help
iamprajot's avatar
iamprajot
Responsive Resident
8 years ago

Here is the Calculated Column not according to the Person Column

Column = IF(Table1[Hours Sold]/Table1[Normal Hours]>1,1,Table1[Hours Sold]/Table1[Normal Hours])

&

Here is the Measure according to the Person Column

Measure = CALCULATE(IF(SUM(Table1[Hours Sold])/SUM(Table1[Normal Hours])>1,1,SUM(Table1[Hours Sold])/SUM(Table1[Normal Hours])),ALLEXCEPT(Table1,Table1[Person]))

  • runemalo's avatar
    runemalo
    Frequent Visitor
    8 years ago

    Hi iamprajot,

     

    Thank you for your response. Unfortunately, it doesn't quite solve it. We need the filtering to be on each person as well. Additionally, there is time to consider here, as the calculation consists of multiple rows within a time frame (adding up to the rows in the example I provided above). Your measure below looks correct, except that the total row should add up to 4.7 and not 1.

     

    I'm trying to think how to better explain it. In R or other programming languages I would have written a lapply or for loop checking for each person in the timeframe given, sum up all sold hours and divide by the expected number (normal hours). If that number is above 1, then 1, ifelse use the calculated number.

     

    Last we want to sum all of these to get the total number of FTE.

     

    Any suggestions?