Simple question on summing across tables RRS feed

  • Question

  • So I've managed to work this bit of it out on my own:

    =CALCULATE(SUM(table2[Amount]),FILTER(table2,table2[IDField] = table1[IDField]))

    This correctly sums up the amounts in table2 in to the categories I have defined in table 1:

    Sales $10,000

    COS: $5,000


    However now I want to bring into play the "Period" field in table 1 so that I can construct something like:

    2008 2009 2010

    Sales 3,000 3,000 4,000

    COS 1,500 1,500 2,000

    I thought it would be simply a case of creating my formula shown above and the break down would then happen automatically when I drop in the period column. But instead the number just repeats itself. What is the best way of doing this? Ideally something where if I chose to break it down by some other field it could cope with that too.

    Thursday, September 26, 2013 9:25 PM


  • Ok answered my own question -- I shouldn't be doing this in a calculated column; I should be creating a measure .. and here's the key bit, I should create it in the Powerpivot window underneath IDField column in table1.

    • Marked as answer by Elvis Long Sunday, October 6, 2013 12:03 PM
    Thursday, September 26, 2013 11:15 PM