Matrix Total Calculation

[jamiefisher]

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.

I'd like to add a column total of budget - comm - actual to show a variance.

I can't define a formula as all the values are held in the same field and the value type is all i have to differentiate between them. Can anyone help please

@

[Greg_Deckler]

@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.

[jamiefisher]

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

[Greg_Deckler]

@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
  __Result

Note, I am making assumptions about your data model because I don't know what it actually looks like.

[jamiefisher]

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