Proration formula behind the scenes

[jl20]

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!

[v-diye-msft]

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?

Please kindly share your dummy pbix for more reference.

[jl20 · Accepted solution]

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

)

Amount to Spread:

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!

[jl20]

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