PowerPivot Sum Using Relationship

[chaserracer83]

Hello there,

I haven't used PowerPivot in two years and I am having a hard time with what should be a simple task...

I have two tables and I have connected them with a key. One table has a unique key for each record, one table can have several records for a single key. I am trying to add a calculated column in the table with the unique keys, that shows the sum of a column form the other table where the keys match.

I actually thought this happened automatically using the SUM function once relationships were setup, but SUM is just showing the total for all records in the other table. I've tried using CALCULATE(SUM($ column from many table),Key from many table = key from single table) and that doesn't work either.

Does anyone know what I'm doing wrong? I've searched all over.

Thank you,
Chase

 

[anvg]

Hi
If you have a relation between your tables it is not required to write a filter statement in CALCULATE. For AdventureWorks traditional database where is exists relation between dimCustomer table and factSales table it will be a calculated column (sum for each customer) in dimCustomer with

=CALCULATE(SUM('factSale'[SaleAmount]))

Regards,

 

[chaserracer83]

That worked like a charm. Thank you!