cancel
Showing results for
Did you mean:
Frequent Visitor

Writing a Measure with a Cap

I am trying to write a measure for project cost, but I would like it with a max hour cap per month.

Worker A and B each salaried employees and over 2080 possible hours in a year have an effective cost \$100/hr, August has 176 possible billable hours.

Worker A works 75 hours = 75 X 100 = Project cost of \$7500

Worker B works 204 hours = 176 X 100 = Project cost of \$17,600

While the revenue for Worker B would be based on 204, I would like the cap to be on the monthly possible hours as that person's cost for a month would not increase based on how many hours they billed since they are salaried.

Can anyone help with this?  Happy to clarify if this doesn't make sense.

3 REPLIES 3
Resolver II

Re: Writing a Measure with a Cap

Would something like this work?

capped = IF(SUM(hoursworked) > 176, 176 * 100, SUM(hoursworked))
Frequent Visitor

Re: Writing a Measure with a Cap

The complexity is I have 30 workers with their different cost rates and each month has varying possible billable hours depending on how many weekdays in the month so I can't hard code each workers cost amount or each months possible hours.

Highlighted
Resolver II

Re: Writing a Measure with a Cap

That makes sense. I'm assuming you have both of those fields already in the daaset? If so, you should be able to just switch them out in the formula above.

Announcements

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors