none
PP Pivot table is not calculating properly. Tables are not linking properly.

    Question

  • Hello,

    I created a power pivot table that is not calculating the data values properly - or as I expect them too.

    I have 22 tables with values and 1 look up table.

    In table 1, called Volume Generated, I have 7 columns. The 4 primary columns I am using are:

    USER NAME, CLIENT NAME, BILLING MONTH, TOTAL VOLUME GENERATED

    In Table 2, called Revenue Generated, I have 4 similar columns

    USER NAME, CLIENT NAME, BILLING MONTH, TOTAL REVENUE GENERATED

    I have a lookup table with the USER NAME.

    Here is the problem I am experiencing. When I put the billing month, user name and client name from the VOLUME table in the rows and the total revenue and total volume in the values, the volume values calculate properly but the revenue value (from the revenue table) doesnt. I see the sum total of the revenue column in every field.

    Same thing vice versa. When I take the attributes from the revenue table and place them in the rows. The total revenue values calculate properly, but I see the sum total of the volume column in each row.

    If I mix the attributes from different table then the values calculate as 0 for everything.

    Can anyone provide some insight as to what I am doing wrong?

    I tried using a different lookup table using the billing date and am getting the same issue.

    Any insight here would be much appreciative.

    Thanks,

    Nathan


    nmss18

    Wednesday, October 02, 2013 7:35 PM

Answers

  • nmss18 -

    You were headed in the right direction with the UserName lookup table.  With multiple tables like that, you need to slice by fields from your lookup table ("dimensions"), and aggregate over fields from your data tables ("facts").  Using your description above, you'd create lookup tables for User, BillingMonth and Client.  Then hook your data tables to each of those.  Simple model would look like this:

    And the resulting pivot would slice by the common ClientName, BillingMonth and UserName fields only from the shared lookup tables.  Not from a single data table.  This is fundamental dimensional modeling.  And with PowerPivot, when relating multiple tables like you are trying to do, slicing by common lookup tables is the key.

    Also, to simplify your model and prevent inadvertently slicing by a field from a non-lookup table (and experiencing the seemingly lack of relationships like you did), the best practice is to hide all of the non-measure fields (e.g. ClientName, UserName and BillingMonth) in each of your data tables.  Those lookup fields should only be available for slicing from your lookup tables.

    The prototype workbook used in the screenshots above is posted in my SkyDrive here for your review.  Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Thursday, October 03, 2013 3:27 AM
    Answerer

All replies

  • nmss18 -

    You were headed in the right direction with the UserName lookup table.  With multiple tables like that, you need to slice by fields from your lookup table ("dimensions"), and aggregate over fields from your data tables ("facts").  Using your description above, you'd create lookup tables for User, BillingMonth and Client.  Then hook your data tables to each of those.  Simple model would look like this:

    And the resulting pivot would slice by the common ClientName, BillingMonth and UserName fields only from the shared lookup tables.  Not from a single data table.  This is fundamental dimensional modeling.  And with PowerPivot, when relating multiple tables like you are trying to do, slicing by common lookup tables is the key.

    Also, to simplify your model and prevent inadvertently slicing by a field from a non-lookup table (and experiencing the seemingly lack of relationships like you did), the best practice is to hide all of the non-measure fields (e.g. ClientName, UserName and BillingMonth) in each of your data tables.  Those lookup fields should only be available for slicing from your lookup tables.

    The prototype workbook used in the screenshots above is posted in my SkyDrive here for your review.  Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Thursday, October 03, 2013 3:27 AM
    Answerer
  • Brent,

    Thanks a million. I followed your instructions, and while it still isnt working the way I need it to, I think I understand why and it would be for something non related to the issue at hand. Your post gave me invaluable information for going forward.

    Thanks,

    Nathan


    nmss18

    Thursday, October 03, 2013 1:12 PM