Forum Discussion
Matrix Total Calculation
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.
Hi sorry for not explaining this properly. I have deatiled table below and the visual outcome I am looking for. I do not have a value column for each type in the table so cannot find a way to create a formual to subtract one figure from another
TABLE Subj CC Period Tran Date Financial Value Balance Class 020201 50300 11 27 Feb 2025 3270.74 AB 020201 50300 11 27 Feb 2025 3270.74 AB 020201 50300 11 27 Feb 2025 -300000.00 BX 020201 50300 11 27 Feb 2025 3270.74 AB 020201 50300 11 27 Feb 2025 3436.55 AB 020201 50300 11 27 Feb 2025 3988.71 AB 020201 50300 11 27 Feb 2025 3988.71 AB 020201 50300 11 27 Feb 2025 351401.00 BX DESIRED VISUAL RESULT Row Labels AB BX Variance 020201 21226.19 51401.00 30174.81 50300 21226.19 51401.00 30174.81 Grand Total 21226.19 51401.00 30174.81