DAX for Sum and Average on category level

[Anonymous]

Hello Community,

I have below Sales metric data source in one database table:

Metric Name	City Name	Target	Actual	Time Period
New Clients	New York	25%	15%	Jan-20
New Clients	New York	25%	10%	Feb-20
New Clients	New York	25%	10%	Mar-20
New Clients	Boston	25%	20%	Jan-20
New Clients	Boston	25%	30%	Feb-20
New Clients	Boston	25%	35%	Mar-20
Returning Clients	New York	40%	55%	Jan-20
Returning Clients	New York	40%	35%	Feb-20
Returning Clients	New York	40%	40%	Mar-20
Returning Clients	Boston	40%	35%	Jan-20
Returning Clients	Boston	40%	25%	Feb-20
Returning Clients	Boston	40%	60%	Mar-20

I am trying to achieve the below result in Matrix Visual which shows totals for the rows but Average for the Metric Name category:

Metric Name	Target	City Name	Jan-20	Feb-20	Mar-20
New Clients	25%	New York	15%	10%	10%
		Boston	20%	30%	35%
		Average	18%	20%	23%
Returning Clients	40%	New York	55%	35%	40%
		Boston	35%	25%	60%
		Average	45%	30%	50%

In matrix visual I find it challenging to have the first two columns and also having average as a sub-total but sum as a total for cities. Any guidance will be appreciated.

Thanks,
Jay

[Greg_Deckler]

@Anonymous Check out MM3TR&R: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150