Forum Discussion
Matrix Total Calculation
jamiefisher Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
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