none
Creating a Threshold Value in PivotChart

    Question

  • Hi,

          I am new to power pivot, so apologies if this (or some variant of) question has been answered before.

    Scenario:

    I have a ResourceDemand table that has records for committed resources to projects for each working day of the year. Each resource can be assigned to more than one project, example below

    Team

    Resource

    Project

    Date

    Committed Hours

    Team A

    John

    Project A

    1<sup>st</sup> March 2017

    4

    Team A

    John

    Project B

    1<sup>st</sup> March 2017

    3.5

    Team A

    Paul

    Project A

    1<sup>st</sup> March 2017

    2

    Team A

    Paul

    Project B

    1<sup>st</sup> March 2017

    3

    Team A

    Paul

    Project C

    1<sup>st</sup> March 2017

    4

    Team B

    Steven

    Project C

    1<sup>st</sup> March 2017

    7







    Each resource has a maximum capacity of 7.5 hours per day. The resource capacity is stored in a Resource table joined to the ResourceDemand table on ResourceID (1-*). I created a hierarchy in powerpivot to drill down from team to resource to date. 

    When I drill as far as the date I want to display the demand for that date range and a capacity limit (7.5 hours). So from the table above

    I drill down from Team A and see John and Paul. I then drill into Paul and see a resource demand for 9 hours (the project field is not in the hierarchy) for March 1st, 6 hours for March 2nd (not in table), 7.5 hours for March 3rd (not in table) etc. i have this working fine.

    I would like in addition to see the 7.5 max capacity for each day as this would visually prompt the user that there is an over allocation for the resource.

    Because of the relationship between the Resource and ResourceDemand tables I cannot figure out how to get the appropriate resource capacity (because a resource can have multiple records for each day against them in the Resource Demand table). I tried adding a capacity column to the ResourceDemand table but in power pivot where there was more than one record for a resource on a specific date it summed capacity to be the number of rows for that particular date * 7.5. So if Paul is selected for March 1st his capacity would be 22.5 hours.

    I would prefer the solution to be adaptable to whatever time period is chosen, so for example if days are listed then 7.5 is capacity, if weeks then 37.5 is listed as max capacity etc.

    Sorry if this is convoluted or cryptic. Any help greatly appreciated.

    Monday, March 20, 2017 9:43 PM

Answers

  • Hi Tony,

    For creating hierarchies, please click Diagram view on Power Pivot model home page. Right click the fields and create hierarchy, please see the screenshot below. For more details, please refer to this article.



    Then create a pivot table, select the hierarchy as row level, the hours as value level. Please see the screenshot below.



    For your other requirements, I am still confusing, please share more details for further analysis.


    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 3:42 AM
    Moderator