cancel
Showing results for
Did you mean:
Helper I

## 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
Super User

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 I

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 I

Thank you. That will work.

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.