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  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 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 #### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling. #### Charticulator Design Challenge

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

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison! Top Solution Authors
Top Kudoed Authors
Users online (4,211)