Calculate billable amount by row

[sbenzaquen]

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 same row by the applicable rate for i. the specific client and ii. specific role/subrole. The challenges that I encounter are:

1. Roles: I use a mapping table to correct/group different roles into their respective teams. For instance, "Implementation TRF" and "Implementation SCF" I'll map it into "Implementation" by using a relationship with a different table. Therefore the corrected role will not appear in the PowerQuery of my main data source. Similarly, I use a different mapping table to classify some team members as junior or senior as a different billable rate would apply.
2. The rates layouts: I cant merge both databases because of the layout of the data. Anyway, you may not need to merge sources with a DAX formula, but I just don't know how to properly do a vlookup in PowerBI with the additional complexity of having two types of roles within structuring (Junior and Senior) and one for implementation teams and not having the corrected roles available within the same PBI spreadsheet.

See the image below for sample data. I have added the columns L-O to simulate the exercise in excel.

This is the formula/relationship I'm ultimately trying to achieve in PowerBI

See below a sample image for the Contract Pricing mapping table per client

@v-eqin-msft  - would be great if you can help me on this one too! 

Thank you,
Salvador

[v-stephen-msft · Accepted solution]

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.

[v-stephen-msft]

Hi @sbenzaquen ,

512.5 is 2.05*250

Subrole is Junior, but why does the rate choose Daily Rate CI?

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]

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]

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

thanks!

[v-stephen-msft]

Hi @sbenzaquen ,

Sorry for my late reply.

It’s a bit cumbersome to manually input the picture data, please share me with your pbix file from your Onedrive for Business.

And what expected result do you want?

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]

Hi Stephen,

Thanks for taking a look. 

See google drive link for the sample data: https://docs.google.com/spreadsheets/d/1Aiz-l2pB6nY7xftwnXSOw3xrjNsxN9Hh4NVohtB6hAk/edit?usp=sharing

The expected result is to have an additional column with the billable amount relating to each entry. The idea is to be able to aggregate the amount by project/end of month.

Let me know if there is anything else i can help with.

thanks,

Salvador

thank you,

salvador