cancel
Showing results for
Did you mean:
Highlighted
Helper III

## Proration formula behind the scenes

Hi all,

I'm having some trouble writing a measure and I was hoping someone could offer a suggestion. I'm nearly done with the model, but can't seem to figure out how to write a measure for [f] below. Basically, in this example, the desired outcome is to pro-rate the negative \$450 adjustment for Project 1 to all Workers who worked on that project, by day. I need the formula to work on multiple views, such that the project/date, etc. don't need to be on the table for it to calculate the Adjusted Amount to Bill.

Here's the measure I wrote for the pro-rated percentage, [e]:

Pro Rata Amount to Bill on Project =
CALCULATE(
[Amount to Bill],
FILTER(
TimeBlock,
TimeBlock[Project Number]=MAX(TimeBlock[Project Number])
))/
CALCULATE(
[Amount to Bill)],
FILTER(
ALL(
TimeBlock),
TimeBlock[Project Number]=MAX(TimeBlock[Project Number])
)
)

And here's the desired outcome. Looking to write a measure for yellow highlighted area. Seem slike a variable might be the best way to "spread" the adjustment, but I'm not very good with those.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Helper III

## Re: Proration formula behind the scenes

I was able to solve using calc columns and variables, as follows:

Relative % =
VAR ProjectNumber = TimeBlock[Project Number]
RETURN
CALCULATE(
SUM(TimeBlock[Time Block \$ to Bill]),
FILTER(
TimeBlock,
TimeBlock[Worker Workday ID] <> "ADJUSTMENT" &&
TimeBlock[Project Number] = ProjectNumber
)

VAR ProjectNumber = TimeBlock[Project Number]
RETURN
CALCULATE(
SUM(TimeBlock[Time Block \$ to Bill]),
FILTER(
TimeBlock,
TimeBlock[Project Number] = ProjectNumber && TimeBlock[Worker ID] = "ADJUSTMENT"
)
)

Thank you for taking a look!
3 REPLIES 3
Helper III

## Re: Proration formula behind the scenes

Perhaps this would be easier to solve using calc columns for the relative percentage per project, and adjusted amount to bill?

Community Support

## Re: Proration formula behind the scenes

Hi @jl20 ,

I'm not quite sure about the results i generated, coz based on the logic, it should be (6.1%*450)+150 = 177.45 not 122.45, am i missing something?

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Helper III

## Re: Proration formula behind the scenes

I was able to solve using calc columns and variables, as follows:

Relative % =
VAR ProjectNumber = TimeBlock[Project Number]
RETURN
CALCULATE(
SUM(TimeBlock[Time Block \$ to Bill]),
FILTER(
TimeBlock,
TimeBlock[Worker Workday ID] <> "ADJUSTMENT" &&
TimeBlock[Project Number] = ProjectNumber
)

VAR ProjectNumber = TimeBlock[Project Number]
RETURN
CALCULATE(
SUM(TimeBlock[Time Block \$ to Bill]),
FILTER(
TimeBlock,
TimeBlock[Project Number] = ProjectNumber && TimeBlock[Worker ID] = "ADJUSTMENT"
)
)

Thank you for taking a look!

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