This correctly sums up the amounts in table2 in to the categories I have defined in table 1:
However now I want to bring into play the "Period" field in table 1 so that I can construct something like:
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.
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.