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] be...
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!
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!