Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sbenzaquen
Helper I
Helper I

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 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.

 

sbenzaquen_0-1616542786546.png

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

sbenzaquen_3-1616543107112.png

 

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

sbenzaquen_2-1616543016096.png

 

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

 

Thank you,
Salvador

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @sbenzaquen ,

 

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

Screenshot 2021-04-01 100231.png

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] )
)

Screenshot 2021-04-01 100516.png

 

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.

View solution in original post

6 REPLIES 6
v-stephen-msft
Community Support
Community Support

Hi @sbenzaquen ,

 

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

Screenshot 2021-04-01 100231.png

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] )
)

Screenshot 2021-04-01 100516.png

 

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
Community Support
Community Support

Hi @sbenzaquen ,

 

512.5 is 2.05*250

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

21.png

 

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.

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

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
Community Support
Community Support

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.

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.