cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## 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. The measure for FTE should be something like
SUM(IF("Hours sold" / "Normal Hours" > 1 , 1 , "Hours sold"/"Normal Hours"), except that is needs to do this for each person and not for the total dataset.. Anyone have a good solution?

How it currently works:

 Person Hours Sold Normal Hours FTE 1 1003 800 1,25 2 600 800 0,75 3 760 800 0,95 4 1008 800 1,26 5 1133 800 1,42 5,63

How I want it:

 Person Hours Sold Normal Hours FTE 1 1003 800 1,00 2 600 800 0,75 3 760 800 0,95 4 1008 800 1,00 5 1133 800 1,00 4,7

PS: The calculation is a bit more complex than above because there is also timing to be considered, hence I can't get calculated columns to work on this

Thank you in advance!

2 REPLIES 2
Responsive Resident

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

Frequent Visitor

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?

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors