Getting Data from the One Side of a One to Many Table Relationship RRS feed

  • Question

  • I have an application that collects absenteeism data for our therapy clients. I have two tables that are linked in a one to many relationship linked on ClientID in PowerPivot.  "tblClient" contains information about the client (name, start date, maximum number of absents...) and the second table contains daily information about the therapy received and if they were absent that day.  I have a chart that shows the  number days that each client was absent.  This is working just fine.  I want to add the maximum number of absents allowed from tblClients to this chart for each Client but I can't get the the actual number from tblClients  to display.  I always get a SUM or a COUNT.  <o:p></o:p>

    To summarize I would want  a second series on this chart (line chart) that shows the maximum number of absents for each client.  <o:p></o:p>

    I am using PowerPivot in Excel 2013.  I would have added some images of the table and chart but when I tried to submit this post it said my account was not verified (but it is!)<o:p></o:p>

    Any help would be appreciated.

    Friday, December 18, 2015 1:32 AM


  • You can drag the field directly to the row or column label area, rather than adding it to the values area where it will be put in an implicit measure.

    Within your Power Pivot model, you can also change the default aggregation for that field to "Do Not Summarize" which would prevent it from being added the values section of the pivot table by default.

    GNet Group BI Consultant

    • Proposed as answer by Michael Amadi Sunday, December 20, 2015 9:23 AM
    • Marked as answer by Michael Amadi Thursday, December 31, 2015 9:20 PM
    Saturday, December 19, 2015 10:41 PM