Average on 2 columns

[obuolys123]

Hello,

I need to calculate the average on section and subsection. I attached the example below.

So I need 2 DAX codes to calculate the average where:
1. Section is in ABC and one subsection (ABBC1). I use SEARCH ABC since I need all sections with the beginning as ABC in one calculation (Because there may be more sections with the same start in the future) and one subsection (which will always be the same).
2. Section is BCA (Because there may be more sections with the same start in the future) and subsection BCCA1 (will always be the same).
I need to use the Average function and not the divide from the distinct count, because there will be cases when there is a section, but it has no values, so it will distort the data.

Tried to use something like: CALCULATE(AVERAGEX(VALUES[section]), (some kind of calculation where the sum of BCA - the value of subsection ABBC1), then SEARCH. But was unable to get the right answer. 

Maybe anyone has some thoughts on it? Thanks.

[v-yangliu-msft · Accepted solution]

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