cancel
Showing results for
Did you mean:
Helper III

## Calculating row average within matrix

Hi.

I'm trying to find average of a row within a matrix. Might be a simple solution, I may be overthinking it.

Got a simple table with the following variables.

When creating the matrix it not giving correct "FTE", instead it culumative sum of each row.

How would I get it to show correctly? Taking average? For example "C" for Jan-Mar only same 2, total for row C should be 2 not sum which is 6.  Thanks.

1 ACCEPTED SOLUTION
Community Support

Hi  @Tevon713 ,

Create a measure as below:

Measure =
VAR _month =
ISINSCOPE ( 'Table'[Month] )
VAR _site =
ISINSCOPE ( 'Table'[Site] )
VAR _region =
ISINSCOPE ( 'Table'[Region] )
VAR _year =
ISINSCOPE ( 'Table'[Year] )
RETURN
IF (
_month,
SUM ( 'Table'[FTE] ),
IF (
_site && NOT ( _month ),
AVERAGEX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = MAX ( 'Table'[Year] )
&& 'Table'[Region] = MAX ( 'Table'[Region] )
&& 'Table'[Site] = MAX ( 'Table'[Site] )
),
'Table'[FTE]
),
IF (
_region && NOT ( _site ),
AVERAGEX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = MAX ( 'Table'[Year] )
&& 'Table'[Region] = MAX ( 'Table'[Region] )
),
'Table'[FTE]
),
IF (
_year && NOT ( _region ),
AVERAGEX (
FILTER ( ALL ( 'Table' ), 'Table'[Year] = MAX ( 'Table'[Year] ) ),
'Table'[FTE]
)
)
)
)
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

4 REPLIES 4
Microsoft

You can try this measure.  Replace with your month column, and it should still yield 2 for those rows but 2 also in the subtotal row.

NewMeasure = AVERAGEX(DISTINCT(Table[Month]), [FTE])

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper III

I tried, getting this error Column FTE cannot be found or may not be used in this expression.

Community Support

Hi  @Tevon713 ,

Create a measure as below:

Measure =
VAR _month =
ISINSCOPE ( 'Table'[Month] )
VAR _site =
ISINSCOPE ( 'Table'[Site] )
VAR _region =
ISINSCOPE ( 'Table'[Region] )
VAR _year =
ISINSCOPE ( 'Table'[Year] )
RETURN
IF (
_month,
SUM ( 'Table'[FTE] ),
IF (
_site && NOT ( _month ),
AVERAGEX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = MAX ( 'Table'[Year] )
&& 'Table'[Region] = MAX ( 'Table'[Region] )
&& 'Table'[Site] = MAX ( 'Table'[Site] )
),
'Table'[FTE]
),
IF (
_region && NOT ( _site ),
AVERAGEX (
FILTER (
ALL ( 'Table' ),
'Table'[Year] = MAX ( 'Table'[Year] )
&& 'Table'[Region] = MAX ( 'Table'[Region] )
),
'Table'[FTE]
),
IF (
_year && NOT ( _region ),
AVERAGEX (
FILTER ( ALL ( 'Table' ), 'Table'[Year] = MAX ( 'Table'[Year] ) ),
'Table'[FTE]
)
)
)
)
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Helper III

Thank you. That will work.

Announcements