Forum Discussion

aashton's avatar
aashton
Helper V
4 years ago

Help with matrix sum by groups

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???

 

Need Help

1 Reply

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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