locked
Connect and Filter a Many to Many Table Relation RRS feed

  • Question

  • Hi,

    My question below references this table in a google sheet.  

    Please view:

     https://docs.google.com/spreadsheets/d/1uOXOUIhgudB_TNUsR-ZKmdEV1uEnpGwmAb2jZCtzD-Y/pubhtml


    We'd like to connect these two tables in POWERPIVOT insuch a way that we could pivot/slice on either criteria to determine the appropiate COSTS field.  We have over 1.5MM lines in the Account Mgmt Table and 18k in Order Table, so we're trying to leverage the processing power of PowerPivot since traditional excel can't manage.

    Example summations we're trying for:

    _Filtering on a specific Install Vendor - what are the costs by AcctID and Emply#

    _Filtering on specific Region Code and Install Time - what are the costs by AcctID and Emply#

    The Order table holds the Quantities that we want to edit and apply to the static Account Mgmt Table.

    Thanks!



    • Edited by 4 horsemen Thursday, February 12, 2015 6:34 PM
    Thursday, February 12, 2015 6:33 PM

Answers

All replies

    • Marked as answer by 4 horsemen Friday, February 13, 2015 5:32 PM
    • Unmarked as answer by 4 horsemen Friday, February 13, 2015 6:39 PM
    • Proposed as answer by Saeid HasaniEditor Friday, February 13, 2015 8:42 PM
    • Marked as answer by 4 horsemen Friday, February 13, 2015 9:53 PM
    Thursday, February 12, 2015 8:41 PM
    Answerer
  • you will need another intermediate table that holds your distinct accounts
    then link them like this:
    OrderTable -> DistinctAccounts <- MgmtTable

    then you can use the approach from the link described above and create ur measures accordingly

    so e.g. OrderCount:=CALCULATE(SUM('OrderTable'[Order Quantity]), 'MgmtTable')
    or Costs:=CALCULATE(SUM('MgmtTable'[Costs]), 'OrderTable')

    hth,
    -gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by 4 horsemen Friday, February 13, 2015 5:32 PM
    • Unmarked as answer by 4 horsemen Friday, February 13, 2015 6:39 PM
    Friday, February 13, 2015 8:19 AM
    Answerer