Simple question on summing across tables


  • 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