locked
Cannot get SUMX(RELATEDTABLE... to work RRS feed

  • Question

  • Hi all,

    I have been playing with the Powerpivot examples found here: http://www.powerpivot-info.com/post/401-microsoft-powerpivot-for-excel-2010-samples. I am using the latest 2012 RTM version (64 bits). In the workbook PowerPivot Tutorial Sample.xlsx I have been trying to add a calculated column on the ProductCategory sheet. No matter what I try I just cannot get it to work...

    This does not work..
    =SUMX(RELATEDTABLE(dbo_FactSales), dbo_FactSales[TotalSales])

    But this works just fine
    =CALCULATE(SUM(dbo_FactSales[TotalSales]))

    As far as I know both commands effectively do the same. Just like Marco Russo states here: http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/c10c6a6b-be25-4db8-9171-b1117978e122/

    I have tried the same formula (and lots of variants) in several different workbooks. I even reinstalled the PowerPivot addin. But to no avail. I keep getting this error:

    For those not famliar with the Dutch language it translates too:

    The syntax for [TotalSales] is wrong.
    The calculated column  'ProductCategory'[CalculatedColumn1] contains a syntax error. Please provide a valid formula.

    It is driving me mad... Any ideas?

    Cheers,
    Erik

    Monday, April 23, 2012 3:43 PM

Answers

  • Thanks for your reply Gerhard.

    I just downloaded the CONTOSO sample en had a look at it. And I found it

    The formula should be:

    =SUMX(RELATEDTABLE(dbo_FactSales); dbo_FactSales[TotalSales]) instead of
    =SUMX(RELATEDTABLE(dbo_FactSales), dbo_FactSales[TotalSales])

    Yes the difference is that the separator should be a semi colon instead of a comma!
    Or at least on my machine.

    Cheers from a happy Erik


    Tuesday, April 24, 2012 7:20 AM

All replies

  • you are right, the statement should work

    also exactly the same statement is used in Contoso sample workbook:
    =SUMX(RELATEDTABLE(FactSales);FactSales[SalesAmount])
    in this workbook also the other formular works just well and returns the same results:
    =CALCULATE(SUM(FactSales[SalesAmount]))

    so i'd guess that the problem is related to your model
    are all your relationships set up correclty? wrong directions maybe?

    have you tried to rebuild the whole model?

    gerhard


    - www.pmOne.com -

    Monday, April 23, 2012 8:00 PM
    Answerer
  • Thanks for your reply Gerhard.

    I just downloaded the CONTOSO sample en had a look at it. And I found it

    The formula should be:

    =SUMX(RELATEDTABLE(dbo_FactSales); dbo_FactSales[TotalSales]) instead of
    =SUMX(RELATEDTABLE(dbo_FactSales), dbo_FactSales[TotalSales])

    Yes the difference is that the separator should be a semi colon instead of a comma!
    Or at least on my machine.

    Cheers from a happy Erik


    Tuesday, April 24, 2012 7:20 AM