Calculating row average within matrix

[Tevon713]

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.

[mahoneypat]

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

[Tevon713]

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

[v-kelly-msft · Accepted solution]

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

Did I answer your question? Mark my reply as a solution!

[Tevon713]

Thank you. That will work.