I'm trying to do what I thought was a relatively simple calculation between 2 tables I have in the new PowerPivot add-on for Excel 2010.
My 2 tables loaded at setup as follows:
Table 1: FooID, BarID, Name
Table 2: BarID, Date
There is a 1 to many relationship between Table 1 and 2.
I want to add a calculated column to Table 1 that just does a sum of all the rows in Table 2.
I've tried this with no luck. The calculation results in null in every column. Excel isn't reporting an error, so it's not that the calculation is invalid, it's just doing somethign I"m not understanding.
=CALCULATE(COUNTROWS(Table2), Filter(Table2, Table2[BARID] = Table1[BARID]))
I also tried something simpler that I thought would also work:
=COUNTROWS(RELATEDTABLE(Table2))
However that also results in null.
I have made sure that table 1 and table 2 have a relationship on BARID.