Hi Folks,
I asked this on the Youtube Channel didnt really explain it that well.
Because we predominantly used Project Web App in work I have never really used Power Query - Until Now!
I am beginning to get my head around using Power Query but have stumbled across a problem when I want to include a variable from a standalone cell in my Custom Calculation.
I'd like to know if I can reference the OHR Cell and PR Cell in my Custom Calc as these may change from project to project. All I can see is the Columns that form the merged table.
Sorry, total noob at this
What I would like my query to look like
| Name | Actual Hrs | Pay rate | SubTotal | Overhead Cost | Profit Cost | Total |
| Blah Blah | 20 | £50 | £1000 | Subtotal*ORH | (SubT+OHR)*PR | Sub+OverheadCost+Profit Cost |
Table in Separate Sheet
| Overhead Rate(OHR) | Profit Rate(PR) |
| 15% | 7% |
Hi Andy,
Please supply a file with a question so we don't have to recreate everything.
See attached file for solution.
I'm not following the calculation for Profit Cost, doesn't seem logical? In your example it would be (1000 + 0.15) * 0.07 ? You can change it in the query as needed.
Regards
Phil
Hi Phil,
I though I had attached the file - obviously not. Profit calculated on the sum of Subtotal inc of Overhead Cost. I see what you mean. I meant Overhead Cost rather than the Rate.
Many thanks for the solution. It had this noob in knots.
Best regards
Andy
No worries.
