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
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 |
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
119 | |
95 | |
87 | |
74 | |
65 |
User | Count |
---|---|
138 | |
112 | |
110 | |
98 | |
94 |