Forum Discussion

Matt22365's avatar
Matt22365
Icon for Resolver III rankResolver III
9 years ago
Solved

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

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

Need Help
  • Matt22365's avatar
    Matt22365
    9 years ago

    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

5 Replies

  • Matt22365's avatar
    Matt22365
    Icon for Resolver III rankResolver III
    9 years ago
    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's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      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?:smileyhappy:

       

      Regards

      • Matt22365's avatar
        Matt22365
        Icon for Resolver III rankResolver III
        9 years ago

        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