Adding Column from one powerpivot table to another from Project Online Odata

[Matt22365]

Hi

I hope you can help, I am new to power pivot so forgive me if this is really easy.

I am trying to create a capacity graph for my department from Odata derived from Microsoft Project Online.

I have 2 tables - ResourceDemandTimephasedDataSet and ResourceTimephasedDataSet.

ResourceDemandTimephasedDataSet has everything I need for the report apart from the Capacity column which is in ResourceTimephasedDataSet 

I have managed to create the graphs for the resource demand but I would like to add a capacity line. As the capacity column is in a different table all it is showing me is the sum capacity rather than actual

When I open the manage function in power pivot it shows the correct value per day, I just cant seem to get that to be shown in the table and charts

I have tried creating relationships between the resource name and capacity which has not worked

I have also tried to create a formula in a calculated column in the table i am using for everything but capacity " =(ResourceTimePhasedDataSet[Capacity]) and I just get an error.

Any help you can give me would be greatly appreciated

Thanks

Matt

[Matt22365]

Hi
With a bit more trial and error I think I have found the error.
If I create a new pivot table for only capacity and use the time by day options from the same table I get the correct result. However, I cannot get the time by date option from the ResourceDemandTimephasedDataSet to link to the ResourceTimephasedDataSet time by date.

Again thank you for your help

Matt

[v-ljerr-msft]

Hi @Matt22365,

According to your description above, you may need to create a corresponding relationship between the two tables, or add a calculated column in ResourceDemandTimephasedDataSet table in this scenario as you have tried. However, without knowing your table structures, it's hard to help further.

So could you post your table structures with some sample data?

Regards

[Matt22365]

Hi @v-ljerr-msft

Thank you for getting back to me. Unfortunately our company wont allow me to put the actual document online due to it having personel details about the resources built into it. However I can you screen shots with the names blanked out.

Below - Top table is a combined table with resource demand (using the timebyday (w/c) from resource demand with the capacity from another table added in. As you can see the capacity is only giving 330. The table beneath it shows capacity with the timebyday(w/c) from its own table and this gives the correct number i.e. 37.5 per week.

I cannot seem to make the combined table show this breakdown correctly.

This is the way I currently have it set up in power pivot. This links reource name between both Resourcetimephased and ResourceDemand (these were connected automatically).

Thank you for your help

Matt

[Matt22365 · Accepted solution]

Hi

I think I have managed to sort this, all I needed to do was to create a new table with just the TimeByDay and create a relationship between both resource tables and this new Timeset table.

Now I just need to figure out why my teams capacities finish mid 2017

Thanks for your help

Matt

[v-ljerr-msft]

Hi @Matt22365,

Great to hear the problem got resolved! Could you accept your reply as solution to help others who may have similar issue easily find the answer and close this thread?

Regards