Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rfwjr64
Frequent Visitor

How to group entries in a visual

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 BIIn Power BIIn Power BI             
GroupPrefix3Part No.Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20TOTALs
RLYABJABJ10101                1,253                    100                1,600                        -                          -                      101                1,616                11,621              15,038
RLYABJABJ10102                1,384                     150                2,400                    151                2,416                    152                     151                2,416                 8,766              16,602
RLYABJABJ10103                    761                      160                     161                     162                     161                2,576                5,911                9,131
RLYABJABJ10107                        190                3,040                    191                3,056                    192                     191                3,056                9,916
RLYABJABJ10108                         200                3,200                5,128                       201                8,729
RLYABJABJ10109                    476                    101                1,616                        -                          -                  4,000                       -                     5,717
RLYABSABS010101                    861                     151                2,416                    152                2,432                    153                2,448                    152                2,432                        -                10,336
RLYABSABS010102                    701                      161                2,576                     162                2,592                     162                2,592                    163                8,408
RLYABSABS010103                    815                       191                3,056                     192                3,072                     192                3,072                9,775
RLYABSABS010104                    302                        201                3,216                        202                3,619
RLYABSABS010109                    587                    102                1,632                     103                1,648                     104                1,664                     105                1,680                7,038
RLYABSABS010110                    663                     152                2,432                     153                2,448                     154                2,464                     155                7,958
RLYABVABV10104                       162                      163                      164                      489
RLYABVABV10105                        192                3,072                        193                3,088                6,545
RLYABVABV10106                    303                        202                3,232                        203                3,637
RLYABVABV10107                1,797                    103                1,648              16,264                     104                1,664                      105                1,680               21,568
RLYABVABV10108                1,614                     153              13,964                      154                2,464                      155                2,480              19,370
RLYABVABV10109                1,952                 6,194              11,664                2,608                      164                2,624                      165              23,419
RLY2AECAEC010105                1,476                 4,684                9,364                    193                  3,280                    194                 17,715
RLY2AECAEC010106                  3,174                7,064                     203              (2,467)                3,280                       204              11,458
RLY2AECAEC010107                     104                1,664                4,764                (1,922)                3,280                       106                7,996
RLY2AECAEC010108                    583                     154                2,464                (1,377)                3,280                2,480                   7,001
RLY2AECAEC010109                    670                      164                2,624                   (832)                3,280                    165                2,640                  8,041
RLY2AECAEC010110                1,840               12,476                     194                3,104                  (287)                3,280                     195                3,120               22,082
RLY2AECAEC010111                1,240               10,936                      204                    258                3,280                      205               14,883
RLY2AECAEC010112                1,367                    105                9,396                      106                1,696                3,280                     107                1,712               16,402
RLY2AEPAEP10104                1,138                 7,856                2,480                      156                    495                      157                2,512              13,656
RLY2AEPAEP10105                1,037                 6,316                    165                2,640                      500                2,656                      167              12,444
RLY2AEPAEP10106                  4,776                     195                      605                    196                   5,772
RLY2AEVAEV010105                  3,236                      205                3,280                    710                       206                7,637
RLY2AEVAEV010106                    302                    106                1,696                        107                1,712                   3,621
RLY2AEVAEV010107                    443                     156                2,496                        157                2,512                  5,321
RLY2AEVAEV010108                    472                      166                2,656                        167                2,672                 5,661
1 REPLY 1
mahoneypat
Microsoft Employee
Microsoft Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.