Average on 2 columns

Hi  obuolys123 ,

Here are the steps you can follow：

1. Create calculated column.

Flag =
SWITCH(
    TRUE(),
CONTAINSSTRING(
    'Table'[Section],"ABC")=TRUE(),1,
CONTAINSSTRING(
    'Table'[Section],"BCA")=TRUE(),2,
    3)

Avaerage ABC =
CALCULATE(AVERAGE('Table'[Value]),FILTER(ALL('Table'),
[Flag]=1))

Avaerage BCA =
CALCULATE(AVERAGE('Table'[Value]),
FILTER(ALL('Table'),
'Table'[Flag]=2))

sum of BCA - ABBC1 =
var _Total_BCA=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Flag]=2))
var _thevalueofsubsectionABBC1=
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Subsection]="ABBC1"))
return
_Total_BCA - _thevalueofsubsectionABBC1

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly