I am using Power BI to extract and report on data from MS Project Online (odata). I am trying to generate a single report that, currently, is being performed from 2 PBI extracts and then combining the data into an Excel Pivot, as follows
Extract 1 – from Timesheet Lines
- ActualWorkBillable
- PeriodEndDate
- ProjectName
- TaskName
- TimesheetOwner
Extract 2 – from Assignments
- ResourceName
From Resources
- ResourceStandardRate (believe this is a custom field, containing the hourly rate)
The data from both extracts is then exported to Excel and a Vlookup used to match the TimesheetOwner to the ResourceName and obtain the hourly rate. This is then used to calculate the cost (ActualWorkBillable multiplied by ResourceStandardRate)
I have tried creating a New Column and using the Related approach (see below), which sort of works but only calculates the cost for some of the entries, however not for the “Top Level” task entries
DailyCost=Related([Resources[ResourceStandardRate])*[ActualWorkBillable]
Selecting other fields, while giving results for all entries, yields really unusually high totals.
Is there a way to create a single query to extract all the data I need ?
TIA
