Forum Discussion

sbenzaquen's avatar
sbenzaquen
Helper I
5 years ago
Solved

Calculate billable amount by row

Hi, I'd like to estimate the billable amount/cost for each entry/row so I can sum them by end of the month. I would like to have an additional column/dax measure that multiplies the hours of the sam...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi sbenzaquen ,

     

    According to the data source file you gave, I created the relationship between the tables.

    billable amount = 
SWITCH (
    MAX ( 'Harvest report - Sample Data'[Subrole] ),
    "Junior",
        MAX ( 'Harvest report - Sample Data'[Hours] )
            * MAX ( 'Contract Pricing'[Daily Rate STR-Jr] ),
    "Senior",
        MAX ( 'Harvest report - Sample Data'[Hours] )
            * MAX ( 'Contract Pricing'[Daily Rate STR-Sr] ),
    "NA",
        MAX ( 'Harvest report - Sample Data'[Hours] )
            * MAX ( 'Contract Pricing'[Daily Rate CI] )
)

     

    You can check details from the attachment.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

sbenzaquen's avatar
sbenzaquen
Helper I
5 years ago

Hi Stephen,

 

Thanks again for taking a look.

 

I just looked more closely at the formula, and as you pointed out, it is wrong. The formula is meant to link "Structuring"&"Junior" with the appropriate mapping key in the contract pricing tab (column C to E) which for this particular case should be column C.

 

Let me know if you need me to amend the online tab, but hopefully it should be fine.

 

Thanks,

Salvador

sbenzaquen's avatar
sbenzaquen
Helper I
5 years ago

I've now amended the online spreadsheet with the correct formula. Would you be able to apply the same rationale in powerbi?

thanks!