How to group entries in a visual

[rfwjr64]

Good Morning, I did a search before posting - but I am not sure how to phrase the question so if there is already a solution, I might have missed it. 

A little background - I have a table that contains three letter characters - for example ABJ, ABS, ABV, AEC, AEP, AEV.  The ABJ, ABS and ABV are the first three characters of part numbers - that have sales values attached to them.  

What I am trying to do is to group these and other values using measure calculations (if that makes sense).  For example, I want to group ABJ, ABS and ABV as RLY.  I want to group AEC, AEP and AEV as RLY2.  Once I do this, I want to be able to use the RLY and RLY2 in matrix tables.  Our data tables are locked down so I can't add a column to the data table.  My only option is to use measures.

So that is where my question comes in ... How do I do it.

Here is a sample

Not in Power BI	In Power BI	In Power BI
Group	Prefix3	Part No.	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	TOTALs
RLY	ABJ	ABJ10101	1,253	100	1,600		-		-	101	1,616			11,621	15,038
RLY	ABJ	ABJ10102	1,384		150	2,400	151	2,416	152		151	2,416		8,766	16,602
RLY	ABJ	ABJ10103	761			160		161		162		161	2,576	5,911	9,131
RLY	ABJ	ABJ10107					190	3,040	191	3,056	192		191	3,056	9,916
RLY	ABJ	ABJ10108						200	3,200	5,128				201	8,729
RLY	ABJ	ABJ10109	476	101	1,616		-		-	4,000	-				5,717
RLY	ABS	ABS010101	861		151	2,416	152	2,432	153	2,448	152	2,432		-	10,336
RLY	ABS	ABS010102	701			161	2,576		162	2,592		162	2,592	163	8,408
RLY	ABS	ABS010103	815				191	3,056		192	3,072		192	3,072	9,775
RLY	ABS	ABS010104	302					201	3,216					202	3,619
RLY	ABS	ABS010109	587	102	1,632		103	1,648		104	1,664		105	1,680	7,038
RLY	ABS	ABS010110	663		152	2,432		153	2,448		154	2,464		155	7,958
RLY	ABV	ABV10104				162			163			164			489
RLY	ABV	ABV10105					192	3,072					193	3,088	6,545
RLY	ABV	ABV10106	303					202	3,232					203	3,637
RLY	ABV	ABV10107	1,797	103	1,648	16,264		104	1,664			105	1,680		21,568
RLY	ABV	ABV10108	1,614		153	13,964			154	2,464			155	2,480	19,370
RLY	ABV	ABV10109	1,952		6,194	11,664	2,608			164	2,624			165	23,419
RLY2	AEC	AEC010105	1,476		4,684	9,364	193			3,280	194				17,715
RLY2	AEC	AEC010106			3,174	7,064		203	(2,467)	3,280				204	11,458
RLY2	AEC	AEC010107		104	1,664	4,764			(1,922)	3,280				106	7,996
RLY2	AEC	AEC010108	583		154	2,464			(1,377)	3,280	2,480				7,001
RLY2	AEC	AEC010109	670			164	2,624		(832)	3,280	165	2,640			8,041
RLY2	AEC	AEC010110	1,840		12,476		194	3,104	(287)	3,280		195	3,120		22,082
RLY2	AEC	AEC010111	1,240		10,936			204	258	3,280			205		14,883
RLY2	AEC	AEC010112	1,367	105	9,396			106	1,696	3,280		107	1,712		16,402
RLY2	AEP	AEP10104	1,138		7,856	2,480			156	495			157	2,512	13,656
RLY2	AEP	AEP10105	1,037		6,316	165	2,640			500	2,656			167	12,444
RLY2	AEP	AEP10106			4,776		195			605	196				5,772
RLY2	AEV	AEV010105			3,236			205	3,280	710				206	7,637
RLY2	AEV	AEV010106	302	106	1,696					107	1,712				3,621
RLY2	AEV	AEV010107	443		156	2,496					157	2,512			5,321
RLY2	AEV	AEV010108	472			166	2,656					167	2,672		5,661

[mahoneypat]

Are you able to add a new table to your model?  If so, make a new table (Enter Data, Excel table, SharePoint list) with two columns for your three-letter codes and the group code it belongs to.  Make a relationship to your other table(s) on the part number code column, and you can then use the group code in your visuals.   If each part number is a part of only one group, it should be a 1:Many relationship from your new table to the other(s).

Regards,

Pat