Forum Discussion

chris_rowley's avatar
chris_rowley
Frequent Visitor
5 years ago
Solved

SumIF less than a specified number

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 ...
Need Help
  • P_D_G's avatar
    P_D_G
    5 years ago

    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

P_D_G's avatar
P_D_G
Resolver III
5 years ago

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