Cannot get SUMX(RELATEDTABLE... to work

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

Answer 1

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 -

Answer 2

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