Forum Discussion
Matrix Total Calculation
I have the following Matrix The columns 1 budget 2 comm 3 actual are all splits of one field. My table has rows of values and types, I am using the types to pull into these three categories. ...
Thanks for coming back - below is the text
| Row Labels | 1 Budget | 2 Comm | 3 Actual | Grand Total |
| 50100-CORPORATE | £650,743.80 | £0.00 | £245,539.94 | £896,283.74 |
| 50300-BUSINESS SERVICES | £740,464.80 | £10,556.01 | £583,606.00 | £1,334,626.81 |
| Grand Total | £1,391,208.60 | £10,556.01 | £829,145.94 | £2,230,910.55 |
I want a calculated total of 1 - 2 -3
However. 1,2 & 3 are calcuated from table like this
50300 Value 100 type 1
50100 Value 200 type 2
50300 Value 100 type 2
So I have a new column setup to say is type is 1 budget if type is 2 actual etc
I hope this explains properly
jamiefisher OK, so are 1 Budget, 2 Comm, 3 Actual are those measures or are those values in a column?
I mean, in general you should be able to create a measure like the following:
Measure =
VAR __1 = SUMX( FILTER( 'Table'[Column] = "1 Budget" ), [Value] )
VAR __2 = SUMX( FILTER( 'Table'[Column] = "1 Budget" ), [Value] )
VAR __3 = SUMX( FILTER( 'Table'[Column] = "1 Budget" ), [Value] )
VAR __Result = __1 - __2 - __3
RETURN
__ResultNote, I am making assumptions about your data model because I don't know what it actually looks like.