How to do a calculated measure based on count of rows?

[Anonymous]

Hi All,

I am stuck in a difficult problem. I am calculating the salary of employees into their full-time salary. However, I noticed that few employees have multiple rows if they worked for different departments and now their salary is being extrapolated as each row is being converted to full-time pay. For eg.

In the image above, Full-time pay is a calculated measure =( 1.0 * Salary)/ Full-Time Equivalent. So employee 1 works only 0.75 but we are converting his salary to full-time salary for this analysis as if they worked 1.0 FTE.

But if we look at employee 2 & 3, they do work 1 FTE but they have seperate rows as they work for different departments. For eg, employee 2 full-time salary should only be 20,000 + 15,000=35,000, but full-time salary is converting their salary into full time salary 2 times as they have 2 rows of data. So their salary is being extrapolated than their actual full-time pay. How can I fix this in Power BI?

[amitchandak]

@Anonymous , Try a measure like

divide( divide( ( 1.0 * [Salary]), [Full-Time Equivalent]), countrows(filter( allselected(Table), Table[employee] =max(Table[employee] ))))

or

sumx(values(Table[employee]) ,
divide( divide( ( 1.0 * [Salary]), [Full-Time Equivalent]), countrows(filter( allselected(Table), Table[employee] =max(Table[employee] ))))
)

[Anonymous]

Hi,

Thanks for the quick solution. I have used this formula,

 divide( divide( ( 1.0 * [Salary]), [Full-Time Equivalent]), countrows(filter( allselected(Table), Table[employee] =max(Table[employee] ))))

However, now for the employees with multiple rows it is only showing the salary of 1st row. So in the example I gave, for employee 2 it is showing full-time pay as $20,000 instead of $35,000.

Is there any way to add the salaries of multiple rows to give the full-time pay?

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You can try formula as below:

Measure1 = 
CALCULATE (
    SUM ( 'Table'[Salary] ) / 1,  // or  SUM ( 'Table'[Salary] ) / SUM ( 'Table'[Full-Time Equivalent] ),
    ALLEXCEPT ( 'Table', 'Table'[Employee] )
)

Best Regards,
Community Support Team _ Eason