locked
Many-To-Many Help RRS feed

  • Question

  • I am trying to understand how I could model the following:

    The property table contains both property and property list names where type determines whether it is a list or a property.  The prop2list table relates the property to the list if a relationship exists.  The total table is at the property level not the list level.  I would like to be able to aggregate at the list level when needed.

    Could anyone suggest a way to handle this?

    Thanks.

    Monday, January 6, 2014 2:55 PM

Answers

  • RELATED always follows your relationships but i wonder why you would relate your Property to PropListId when you have also PropId in your table??
    does not really make sense to me
    I expected that the table PropToList maps Properties to List
    if a Property is a list the list has the same ID as the Property

    you can also replace RELATED by LOOKUPVALUE similar to the ListCode-Column

    hth,
    gerhard


    - www.pmOne.com -

    • Proposed as answer by Elvis Long Friday, January 10, 2014 3:18 AM
    • Marked as answer by Elvis Long Monday, January 13, 2014 2:49 AM
    Thursday, January 9, 2014 7:55 AM
    Answerer

All replies

  • When you don't store any amount(Total table) at List level, why do you need to aggregate at List Level ?

    If "Total" table store amount for both Property and List Type then we can write as,

    SELECT P2.PROPLISTID, SUM(P3.AMT) AMT FROM [PROPERTY] P1
    INNER JOIN PROP2LIST P2 ON P1.PROP = P2.PROPLISTID
    INNER JOIN TOTAL P3 ON P2.PROPID = P3.PROPID
    WHERE P1.TYPE='LIST'
    GROUP BY P2.PROPLISTID

    or

    SELECT P1.PROP, SUM(P3.AMT) AMT FROM [PROPERTY] P1
    INNER JOIN TOTAL P3 ON P1.PROP = P3.PROPID
    WHERE P1.TYPE='LIST' or P1.TYPE='Property'
    GROUP BY P1.PROP


    Regards, RSingh

    Monday, January 6, 2014 3:28 PM
  • Thanks for your feedback.

    I am trying to do this in Power Pivot.  The total table stores the amt's at the property level.  Now in power pivot i want to see it at the property level but still have the option of seeing the aggregate amt's at the list level say by filtering it by list.  Does that make sense?

    Thanks.

    Monday, January 6, 2014 5:21 PM
  • do you have some sample data or sample workbook?

    its pretty hard to guess the relationships based on your column-names


    - www.pmOne.com -

    Tuesday, January 7, 2014 4:36 PM
    Answerer
  • Not sure how to get you a copy of the workbook?  I don't see a way to attach a file.  I did a couple of screenshots..not sure if that helps.

    Thanks

    PropertyTable

    PropToListTable & TotalsTable

     
    Tuesday, January 7, 2014 7:54 PM
  • OK, i would do the following:

    create two calculated columns in your PropToListTable as
    PropertyCode=RELATED('Property'[PropCode])
    ListCode=LOOKUPVALUE('Property'[PropCode],'Property'[PropID],PropToList[PropListID])

    then create a hierarchy as ListCode --> PropertyCode

    finally create a calculated measure as
    Amount:=CALCULATE(SUM('Totals'[MTDAmt]), CALCULATETABLE('Property', PropToList))

    make sure to use the new hierarchy to analyze your data

    hth,
    gerhard


    - www.pmOne.com -

    Tuesday, January 7, 2014 11:03 PM
    Answerer
  • Thanks.  Two things: the column heading in the Totals table should read PropID since it is at the property level only; the other thing is that what I use the related formula for propertycode it is using the proplistid  instead of the propid so that now PropertyCode and ListCode are the same.  Is there a way to say which column you want PP to use in the RELATED formula or is based on the relationship that is setup?
    Wednesday, January 8, 2014 3:15 PM
  • RELATED always follows your relationships but i wonder why you would relate your Property to PropListId when you have also PropId in your table??
    does not really make sense to me
    I expected that the table PropToList maps Properties to List
    if a Property is a list the list has the same ID as the Property

    you can also replace RELATED by LOOKUPVALUE similar to the ListCode-Column

    hth,
    gerhard


    - www.pmOne.com -

    • Proposed as answer by Elvis Long Friday, January 10, 2014 3:18 AM
    • Marked as answer by Elvis Long Monday, January 13, 2014 2:49 AM
    Thursday, January 9, 2014 7:55 AM
    Answerer