Show Either Total or Average in Matrix Total

[Jaweed]

Hi Friends

I am seeking your valuable assistance.

I am able to design a matrix in powerbi, with totals showing properly at required hierarchy levels. My problem is that for some columns, I need to do a SUM in the Total row,  while for others I need to calulate the average instead. Examples, for Revenue it makes sense to get the Total while for % Profit, the 'Total' should show the AVERAGE of the rows.

I attach a sample Test data with the desired output of the matrix. In the sample data, when column [Unit of Measure] = Units, I need to sum, when the column = %, then I need to average. 

Thank you for your help. 

Company	Month	Metric	Value	Unit of Measure
CPYA	24-Jan	Revenue	20000	Units
CPYA	24-Jan	% Profit	60	%
CPYA	24-Jan	No of Complaints	3	Units
CPYA	24-Jan	% Leavers	6	%
CPYB	24-Jan	Revenue	25000	Units
CPYB	24-Jan	Percentage  Profit	30	%
CPYB	24-Jan	No of Complaints	6	Units
CPYB	24-Jan	% Leavers	4	%
CPYA	24-Feb	Revenue	10000	Units
CPYA	24-Feb	% Profit	20	%
CPYA	24-Feb	No of Complaints	2	Units
CPYA	24-Feb	% Leavers	8	%
CPYB	24-Feb	Revenue	12000	Units
CPYB	24-Feb	% Profit	10	Percentage
CPYB	24-Feb	No of Complaints	1	Units
CPYB	24-Feb	% Leavers	2	%
Desired output in Matrix
		Revenue	% Profit	No of Complaints	% Leavers
Consolidated		67000	30	12	5
CPYA	24-Jan	20000	60	3	6
	24-Feb	10000	20	2	8
	Total/Average	30000	40	5	7
CPYB	24-Jan	25000	30	6	4
	24-Feb	12000	10	1	2
	Total/Average	37000	20	7	3
Grand Total		67000	30	12	5

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.