Grouping Similar Rows

[StuartSmith]

Looking for ideas on how I could handle some data similar to the below...

Region		Country		City		Building		Headcount
EMEA		UK		London		LON01		5
EMEA		UK		London		LON02		0
EMEA		UK		London		LON03		1
EMEA		UK		Manchester		MAN01		4
EMEA		Spain		Madrid		MAD01		2
EMEA		Spain		Madrid		MAD02		0
EMEA		Spain		Barcalona		BAR01		7

and looking to maybe create a new column that displays the "Cluster Headcount" for all rows where the "Building" has the same 1st 3 characters.

Region		Country		City		Building		Headcount		Cluster Headcount
EMEA		UK		London		LON01		5		6
EMEA		UK		London		LON02		0		6
EMEA		UK		London		LON03		1		6
EMEA		UK		Manchester		MAN01		4		4
EMEA		Spain		Madrid		MAD01		2		2
EMEA		Spain		Madrid		MAD02		0		2
EMEA		Spain		Barcalona		BAR01		7		7

and also a new table similar to...where it groups all the values of "Headcount" for each building with the same 1st 3 Cahracters.

Region		Country		City		Building		Cluster Headcount
EMEA		UK		London		LON		6
EMEA		UK		Manchester		MAN		4
EMEA		Spain		Madrid		MAD		2
EMEA		Spain		Barcalona		BAR		7

Thanks in Advance,

[rsbin · Accepted solution]

@StuartSmith ,

Create 2 Calculated Columns:

BuildingAbbr = LEFT( [Building], 3 )

ClusterHeadcount = CALCULATE( SUM( [Headcount]),
                          ALLEXCEPT( Headcount, Headcount[BuildingAbbr] ))

Then create a Table Visual dragging in whatever fields you want to see.

Trust this is what you are looking for.

Regards,