locked
Calculated Column (SUM, RELATED) RRS feed

  • Question

  • I am new to this forum and to PowerPivots in Excel.  I am trying to create a calculated column that will allow me to summarize data from a detail sheet into another sheet.  The sheets have an established relationship via the Project # column(the image of the relationship diagram can't be posted until I verify my account and there are no instructions included to tell me how to do that).

    _Perpetual is related to _12500 through the PROJECT # column and to _12501 through the calculated column PROJECT#2 which is a copy of the PROJECT # column.  Line by line detail is in the _Perpetual sheet ("many"); unique instance of project number is in the _12500 sheet (I'm betting on the idea that if I can get this to work for the _12500, I can get it to work for the _12501, as well).

    What I'm trying to do is to get a sum by project number column to appear in the _12500 table based on the TOTAL EXT COST column in the _Perpetual.  What I'm having trouble with is getting the DAX "=SUM(RELATED(" to work.  I'm using Excel 2013 and the MSDN tutorial is written for 2010 so I'm never sure if I just don't understand the syntax or the syntax given isn't correct anymore.

    So far, I've tried "=SUM(RELATED(_Perpetual),)" and "SUM(RELATEDTABLE(_Perpetual),)" as well as SUMX with both RELATED and RELATEDTABLE. The calculated column is in the _12500 (the one with the unique list of project numbers). I'm expecting to be able to specify the column from the _Perpetual (between the last comma and the last paren of the above formulas) that I want to have summed ([TOTAL EXT COST]) and the only column choices I'm given are the ones from _12500.

    Sorry about the long post . . . but I appreciate any help you can offer.

    Thursday, January 21, 2016 6:14 PM

Answers

  • Fixed it.  Used CALCULATE(SUM.  Worked perfectly!
    • Marked as answer by David_JunFeng Friday, January 29, 2016 9:49 AM
    Thursday, January 21, 2016 8:59 PM

All replies

  • Fixed it.  Used CALCULATE(SUM.  Worked perfectly!
    • Marked as answer by David_JunFeng Friday, January 29, 2016 9:49 AM
    Thursday, January 21, 2016 8:59 PM
  • Hi, MellahH

    congratulation, I am glad to hear you have resolved your issue by yourself. This is helpful for other community.

    In addition, this is the forum to discuss questions and feedback for Excel for Developers, if you have any issue about Excel, please feel free to post them to Technet forum for Excel IT Pro Discussions:

    https://social.technet.microsoft.com/Forums/office/en-US/home?forum=excel

    Thanks for your understanding.


    Friday, January 22, 2016 2:08 AM