locked
PowerPivot Relationship-Pivot Table Not working as one would think RRS feed

  • Question

  • I am hoping someone can help me understand something.  I am new to PowerPivot and have been reviewing PowerPivot tutorials, reviewing Blogs & the Forum topics and maybe I am just not understanding why my powerpivot is not working as one would think it should.  I have a one to many relationship power pivot.  Two Tables (table one "Scoring") (table two "Insurance") 

    =================================================

    Table one "Scoring" fields

    Key Id Score
    1 0.92
    2 1.03
    3 1.17
    4

    1.02

    =========================================================

    Table Two "Insurance" fields

    KeyId        Division           Segment             Quoted          Sold           Not Sold

    1               Midwest           Middle                 1                   1

    2               Midwest           Middle                 1                                        1

    3               West                Middle                 1                   1

    4               West                 Middle                1                    1

    ======================================================

    I created a pivot Table:

    I used the "Division" field from the "Insurance" Table as the Slicer and/or Report filter

    I added "Division" field from the "Insurance" table as the "Row"

    I add to the "Values" section in the pivot the following fields: 

    From "Insurance"Table, set to sum:  Sold, Not Sold

    From "Scoring" Table set to sum:  Score

    What I get is this:

    ==================================================

                                          Sold             Not Sold                  Score

    Midwest                           1                     1                           4.14

    West                                2                                                 4.14

    Total                                3                     1                           4.14

    ===================================================

    The data from the "Insurance" table are summing up correctly but It looks like the all the scores for the 'Scoring" table are summed together instead of what I thought I would see which is the sum of the Midwest scores summed together(1.95) and the sum of the West summed together (2.19) to show under Score by Division. When I filter on Divison West I see only the west data but still see the whole Score 4.14 total.

    can someone please help me?  I guess I would have that if these tables are related now and I filter using data from one table then this filter should work on both table's data.  If the way I am trying to use this pivot table is changing my one to many relationship to a many to many can someone please explain.  Sorry I am just having a tough time with this and very frustrated.  It just seems like PowerPivot should be able to handle this easily.

    Anyhelp would be wonderful.

    Tuesday, June 19, 2012 3:28 PM

Answers

  • Hi JoanneO -

    That behavior is caused by the direction you defined the relationship and the way you are trying to slice the data.  PowerPivot relationships behave like lookups.  From the many side to the one side.  The "many" side typically contains the values you'd like to aggregate (fact table).  And the "One" side typically is the table that contains the fields you'd like to slice by (dimensions: reporting labels you'd put on the rows axis and group by).

    Your data seems to be a mixture, but regardless, there are several approaches that you can use to make your model work:

    1. Delete the existing relationship and define a new one from Scoring[KeyId] to Insurance[KeyId].  With this new relationship created, you can slice by the fields in Insurance table and should get the expected results in your pivot

    2. If the granularity is exactly 1 to 1 between Insurance and Scoring tables, you could simply add the Score column to your Insurance table before importing to PowerPivot.  This would allow you to skip the relationshps

    3. If the granularity is exactly 1 to 1 between Insurance & Scoring tables, but you can't or don't want to combine the data prior to importing to PowerPivot, you could keep the existing relationship (as is from Insurance to Scoring table), and define a calculated column in Insurance using:

    =RELATED(Scoring[Score])

    Then you'll be able to slice the score from within the Insurance table and won't have the issues going across the relationship. 

    If you go with option 3, I'd also suggest hiding the entire Scoring table (right click and "Hide from Client Tools").  That way, no one will run into the same issues you already hit when trying to SUM score from the Scoring table instead of the calculated column in Insurance.

    Hope that helps.  Post more details if you have any other issues.


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


    Monday, June 25, 2012 1:17 AM
    Answerer

All replies

  • Hi, I think creating a relationship in powerpivot will work for this. Try and check
    Thursday, June 21, 2012 1:46 PM
  • Hi JoanneO -

    That behavior is caused by the direction you defined the relationship and the way you are trying to slice the data.  PowerPivot relationships behave like lookups.  From the many side to the one side.  The "many" side typically contains the values you'd like to aggregate (fact table).  And the "One" side typically is the table that contains the fields you'd like to slice by (dimensions: reporting labels you'd put on the rows axis and group by).

    Your data seems to be a mixture, but regardless, there are several approaches that you can use to make your model work:

    1. Delete the existing relationship and define a new one from Scoring[KeyId] to Insurance[KeyId].  With this new relationship created, you can slice by the fields in Insurance table and should get the expected results in your pivot

    2. If the granularity is exactly 1 to 1 between Insurance and Scoring tables, you could simply add the Score column to your Insurance table before importing to PowerPivot.  This would allow you to skip the relationshps

    3. If the granularity is exactly 1 to 1 between Insurance & Scoring tables, but you can't or don't want to combine the data prior to importing to PowerPivot, you could keep the existing relationship (as is from Insurance to Scoring table), and define a calculated column in Insurance using:

    =RELATED(Scoring[Score])

    Then you'll be able to slice the score from within the Insurance table and won't have the issues going across the relationship. 

    If you go with option 3, I'd also suggest hiding the entire Scoring table (right click and "Hide from Client Tools").  That way, no one will run into the same issues you already hit when trying to SUM score from the Scoring table instead of the calculated column in Insurance.

    Hope that helps.  Post more details if you have any other issues.


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


    Monday, June 25, 2012 1:17 AM
    Answerer