Forum Discussion

jl20's avatar
jl20
Icon for Helper IV rankHelper IV
7 years ago
Solved

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!

Need Help
  • jl20's avatar
    jl20
    7 years ago

    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!

3 Replies

  • jl20's avatar
    jl20
    Icon for Helper IV rankHelper IV
    7 years ago

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

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      jl20
      Icon for Helper IV rankHelper IV
      7 years ago

      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!