Help with matrix sum by groups

[aashton]

Hello,

I have a table set-up as below:

I'd like to use a matrix to set it up similar to a pivot table:

I've tried different ways and can't get it to work.  I've put BDO and State in the Rows, and have created measures for totals of phase 1, 2, and 3 and put that in the values.  But for example, the total for Phase 1 is repeating the grand total, not the total by BDO by State.  This is the formula I used:  

Total Phase 1 = CALCULATE(SUM('BDO Goals'[Phase 1 Goal]), ALLEXCEPT('BDO Goals','BDO Goals'[BDO],'BDO Goals'[State])).

This should work. Not sure why it doesn't???

[PaulDBrown]

The measure you need is a simple SUM for each Phase. The fields in the rows provide the filter context you need

Having said that, I would model your data differently.

Table 1 would include BDO, State and the Phases Unpivotted. You can then create a simple SUM for the values and set up the matrix with the phases field (from the diemnsion table- see below) as the columns for the matrix.

Table 2 would exclude the phases columns. 
Table 3, 4 and 5 would be dimension tables for Phases, BDO and State, with one-to-many relationships between the corresponding fields in Table 1 and 2.

However this decision very much depends on what you need to depict in the report