Display Total in Matrix visual row

[giramswa]

Hi All,

I have a table with Project data as below:

Customer	Project_Num	Project_Status	Allotted_Hrs	Used_Hrs	Remaining_Hrs	Employees_Assigned
C1	P1	Open	100	50	50	Emp1
						Emp2
	P2	Closed	200	100	100	null
	P3	Closed	300	150	150	null
C2	PM1	Open	150	50	100	Emp3
						Emp4
	PM2	Open	250	100	150	Emp5
						Emp6
	PM3	Cancelled	350	200	150	null
C3	PRJ_1	Open	1000	500	500	Emp7
	PRJ_2	Closed	2000	1200	800	null
	PRJ_3	Open	3000	2600	400	Emp8
						Emp9

I want to display above data in a matrix visual as below:

Customer	Total Alloted_Hrs	Total Used_Hrs	Total Remaining_Hrs	Project_Status	Project_Num	Num_Of_Employees
C1	600	300	300	Open	P1	2
				Closed	P2	0
					P3	0
C2	750	350	400	Open	PM1	2
					PM2	2
				Cancelled	PM3	0
C3	6000	4300	1700	Open	PRJ_1	1
					PRJ_3	2
				Closed	PRJ_2	0

Reason for using Matrix is the requirement where we need to show total hrs per customer, group all projects with similar status under each customer. Currently I have placed Customer, Project Status, Project Num under rows.

Matrix visual does not allow placing a measure under Rows. 'Switch values to rows' will show every value under row which I don't want. How can I achieve this?

[lbendlin · Accepted solution]

Your sample data is not in a usable format.  Please confirm that these transforms are still correctly representing the data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDBCsIwEER/RXLuodm0tZ6DF6EYvJYgornFWBD/302WZqMEesikhcmbnZ1noaVohIlyXlzAS7Ytap/l+FyksM0sdvhTO2iAbDCAov3r7R74AYklCw0f79msSrMiW8+azTpSzVROyQMSGKdQW2N2nDwBs6DAyLVzvwUbCljqcQt35z1VSRRoa1Wi11xO19+VUxtWDNhzANr/95rQlDBWNosvFAco8sOQrm5NGLc6HoS1Xw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Project_Num = _t, Project_Status = _t, Allotted_Hrs = _t, Used_Hrs = _t, Remaining_Hrs = _t, Employees_Assigned = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Customer","Project_Num","Project_Status","Allotted_Hrs","Used_Hrs","Remaining_Hrs"}),
    #"Filled Down1" = Table.FillDown(#"Replaced Value",{"Customer","Project_Num","Project_Status","Allotted_Hrs","Used_Hrs","Remaining_Hrs"})
in
    #"Filled Down1"