Need average in row and total of average in sub total column

[sajjadkhan253]

Hi Team, @amitchandak sir,

question:

location	status	qualified	intial	NA	total sum of average
offshore	average>>>	15	20	9	44
	a	10	15	10
	b	20	25	8

please help me to get desired result 

[v-jianboli-msft · Accepted solution]

Hi @sajjadkhan253 ,

Based on your description, I have create a simple sample:

Apply the measure to a matrix visual:

Measure =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[ID] ) && ISINSCOPE ( 'Table'[Status] ), SUM ( 'Table'[Value] ),
    ISINSCOPE ( 'Table'[Status] ) && NOT ( ISINSCOPE ( 'Table'[ID] ) ), AVERAGE ( 'Table'[Value] ),
    NOT ( HASONEVALUE ( 'Table'[Status] ) ) && NOT ( HASONEVALUE ( 'Table'[ID] ) ),
        SUMX (
            SUMMARIZE (
                'Table',
                'Table'[Status],
                "Average",
                    CALCULATE (
                        AVERAGE ( 'Table'[Value] ),
                        FILTER ( 'Table', [Status] = EARLIER ( 'Table'[Status] ) )
                    )
            ),
            [Average]
        )
)

Change the name of Row and Column Subtotals:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.