SumIF less than a specified number

[chris_rowley]

Hi all

I have a table that contains data pulled from our time recording system. After some manipulation I have these fields:

• Total_Hours_Per_week
• Hourly_Rate
• Max_hours_Per_Month

Each staff member could have multiple rows per financial week. For example i could log a total of 37.5 hours across 4 different projects. another staff member may log 55.5 hours across 3 different projects.

Example Data Set

Staff_Name	Week	Total_Hours_Per_Week	Month	max_hours_per_month	Hourly_rate
staff1	week1	10	APR	187.50	15.35
staff1	week1	5	APR	187.50	15.35
staff1	week1	15	APR	187.50	15.35
staff1	week1	7.5	APR	187.50	15.35
staff2	week1	15	APR	187.50	13.25
staff2	week1	15	APR	187.50	13.25
staff2	week1	7.5	APR	187.50	13.25

The hourly rate is self explanatory. the max hours per month is total hours per week of 37.5 * number of weeks in a month. if a 4 week month max hours is 150, if a 5 week month max hours is 187.5

so essentially i need a measure that performs this calculation as i will then put it into a visual which shows a breakdown of cost per month per staff member:

if sum of total hours > max hours per month

then max hours * hourly rate

else sum of total hours * hourly rate

i have tried so may different alternatives to get the correct figures but keeping failing. any help would be greatly appreciated.

thank you

[v-eqin-msft]

Hi @chris_rowley ,

According to my understand, you want to calculate the Hours * Rate based on two different conditions, right?

You could follow these steps:

1.Create a DateTable with Date , Month , Total weekNum per Month columns like this:

2. Create a relationship between two tables based on Month column.

3.Use the following formula:

Result =
VAR _actualHoursTotal =
    SUM ( 'Table'[Total_Hours_Per_Week] )
        * MAX ( 'DateTable'[Total weekNum per Month] )
RETURN
    IF (
        _actualHoursTotal <= MAX ( 'Table'[max_hours_per_month] ),
        _actualHoursTotal * MAX ( 'Table'[Hourly_rate] ),
        MAX ( 'Table'[max_hours_per_month] ) * MAX ( 'Table'[Hourly_rate] )
)

My final visualization looks like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[chris_rowley]

unfortunatly both solutions are still no capping the total number of hours at the maxium allowed for the month. as mentioned i do have a seperate table for staff costs and for the month details including a relationship with the tables aswell.

I have updated the formula to this:

Cost_V2 = if(

sum(ABR_DATA[Total_hours_Per_week]) >

min(Calendar_2[Working Hours Per Month]),

min(Calendar_2[Working Hours Per Month]),

sum(ABR_DATA[Total_hours_Per_week])

)

* min('20/21_Staff_Costs'[Hourly_Staff_Cost])

i just cant seem to get the hours per month capped at the max allowed for the month

[P_D_G]

I'm not sure that I fully understand the issue.

Is that the measure only returns Total_hours_Per_week * Hourly_Staff_Cost as the result?

Could you upload to Dropbox/GDrive a .pbix file with dummy data? 

Thanks,

PDG

[chris_rowley]

hi so unfortunatly i am unable to upload a file due to time restrictions.  ill try and explain a little better.

basically each staff member works a certain amount of hours per month however for financial reasons in the back ground the amount of hours is capped at the maximum for the month. basically finance will say if a staff member works 200 hours in the month of april then a cost per hour is only applied to the max number of hours for that month i.e. 187.50 hours for april. overtime is handled speratly.

the timesheet data will reflect the time recorded per staff member per week per the project they have been working on. so regardless of project i can get the total number of hours worked in this example 200. i then need to say if this total is greater than the max allowed for the month which is 187.50 then i need to cap the staff members total number of hours at 187.50 * this by the hourly staff cost to give me the total figure.

example:

staff member 1 total hours worked 200 in april

staff member 2 total hours worked 185 in april

max hours in april 187.50 varies per month (e.g. may = 150 hours)

hourly rate 15.50 - varies per staff member

formula = if 200 > 187.50 then 187.50 * 15.50 else if total hours < 187.50 then total hours * hourly rate.

staff member 1 - total cost 2906.25 (187.50 * 15.50)

staff member 2 - total cost 2867.50 (185 * 15.50)

hopefully this explains it a little better

[P_D_G · Accepted solution]

Thanks for the expmalation. This part is clear. I'm not sure why the solution we provided is not working, which part is expected to work differnetly.

Please have a look at this PBIX file and point me in the right direction. This is what I have:

Thanks

[chris_rowley]

thank you to P_D_G

after creating some new visuals and a couple of tweaks with filters the formula now works correctly. my final figures are still slightly out but i fear there maybe something in my underlying data that could be at fault.

thank you for your assistance it has been much appreciated.

[P_D_G]

hi @chris_rowley ,

creating this measure should work:

if(

sum('Table'[Total_Hours_Per_Week] ) > min('Table'[max_hours_per_month]),

min('Table'[max_hours_per_month]),

sum('Table'[Total_Hours_Per_Week])
)

* min('Table'[Hourly_rate])

However, I'd reconsider restructuring your data, for example creating a separate tables with the
1) Month_Name & Max_Hours_per_Month values
2) Staff_Name &Hourly_rate

This might inprove the performance of your model.

Best,

PDG

[AllisonKennedy]

Do you have a DimDate table?

You can try something like;

Pay =
VAR _MaxHrs = MAX(table[hours per month])
RETURN
IF( sum(Table[hours]) >_MaxHrs , _MaxHrs* MAX(Table[Hourly Rate]),
sum(Table[hours] *MAX(Table[hourly rate])

How have you done the calculations for the three columns you already have? There may be a more efficient way to get this result than using those calculations.