Forum Discussion
Compare Rows on Value Difference and Highlight Products belonging to Same group
Hi Friends,
I have products grouped as per category recipes as below :
| Group Name | Product |
| A | A_1 |
| A | B_1 |
| A | C_1 |
| X | X_1 |
| X | Y_1 |
| X | Z_1 |
And in PowerBI Matrix , I have values presented as below :
| Country Name | Category | Jun-22 | May-22 | Diff > 0 |
| Country1 | A_1 | 120 | 110 | 10 |
| Country1 | X_1 | 30 | 60 | (30) |
| Country1 | B_1 | 100 | 100 | - |
| Country1 | UVA WPP | 600 | 550 | 50 |
| Country2 | Y_1 | 80 | 80 | - |
| Country2 | C_1 | 25 | 22 | 3 |
| Country2 | Z_1 | - | - | - |
| Country2 | B_1 | - | - | - |
I want , 1) if any product belonging to a specific group say A, is > 0 or < 0, then all the product rows beloinging to that group should be highlighted with same color..
For eq : 1) In the Matrix table above , Product A_1 (belonging to Group A) has difference > 0, then all the products belonging to Group A , will be highlighted with the same color.
2) Also, is it possible to have a color filtering to see all the same color rows below each other.
Thank you so much in advance...
10 Replies
Yes, this should be possible. It's called a "Filtering up" pattern.
Please provide sanitized sample data that fully covers your issue. I cannot help you if you do not provide usable sample data.
If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Pardon me for being messy on providing information. Its my first time on the forum and will streamline for future question.
Below is the screenshot of the expectations of output
Here, NEON BLUE and NEON INDIGO belong to same group "NEON" , and since the Diff value in NEON BLUE is <> 0, it highlights another product from same GROUP whose values are <> 0.
and that follows for other group products too with different highlighted colors.
Please let me know if you need more information. Thank you in advance
Thank you for the clarifications.
Note that your sample group data is not covering your product data.
Please let me know how that should be handled. Please also explain the color highlighting rules again. You didn't specify which color you want?
Thank you Ibendlin for your reply. Seems I was not clear in my explanation. sorry for the confusion. Let me try again.
Below is my Group data where the products are grouped in "1" , "2", "3" and "4"
No. Product 1 ERIOSLOW BLACK M 1 ERIOSLOW BLUE 3R 1 ERIOSLOW NAVY M 1 ERIOSLOW RED 2B 1 ERIOSLOW RED B 1 ERIOSLOW YELLOW 5G 1 ERIOSLOW YELLOW R 1 ERIOSLOW FIX-01 2 NEWLAN BLUE CD-P 2 NEWLAN BLUE P OPTIFLOW 2 NEWLAN RED CD-P 2 NEWLAN RED P OPTIFLOW 2 NEWLAN YELLOW CD-P 2 NEWLAN YELLOW P OPTIFLOW 2 ALBEGAL PLUS 3 LINDASOL BLACK CE 3 LINDASOL BLUE 3G C 3 LINDASOL BLUE 3R 3 LINDASOL BLUE CE 3 LINDASOL DEEP BLACK CE-R 3 LINDASOL DEEP BLACK CE-2B 3 LINDASOL GOLDEN YELLOW CE-01 3 LINDASOL NAVY CE 3 LINDASOL RED 6G 3 LINDASOL RED CE 3 LINDASOL YELLOW 4G 3 LINDASOL YELLOW CE 3 ALBEGAL B 3 ALBEGAL E3-B 4 TERABOTTOM BLACK HL-BL 4 TERABOTTOM BLACK HL-NF-01 4 TERABOTTOM BLACK HL-SF 4 TERABOTTOM BLACK LF-01 4 TERABOTTOM BLUE HL-B-01 150% 4 TERABOTTOM ORANGE HL 4 TERABOTTOM RED HL 4 TERABOTTOM YELLOW HL-G-01 150% Below is my RAW data. This data is for State : Maharashtra
Month WD10-June WD10-May Product Name 202206 97 85 ERIOSLOW BLACK M 202206 11 15 ERIOSLOW BLACK M 202206 40 79 ERIOSLOW BLACK M 202206 76 73 ERIOSLOW BLACK M 202206 50 37 ERIOSLOW BLACK M 202206 0 34 ERIOSLOW BLACK M 202206 40 29 ERIOSLOW BLACK M 202206 57 11 ERIOSLOW BLACK M 202206 73 83 ERIOSLOW BLACK M 202206 7 13 ERIOSLOW BLACK M 202206 62 69 ERIOSLOW BLACK M 202206 1 26 ERIOSLOW BLACK M 202206 36 47 ERIOSLOW BLACK M 202206 59 15 ERIOSLOW YELLOW R 202206 0 45 ERIOSLOW BLACK M 202206 65 21 ERIOSLOW YELLOW R 202206 50 94 ERIOSLOW BLACK M 202206 21 58 ERIOSLOW BLACK M 202206 3 26 ERIOSLOW BLACK M 202206 34 94 ERIOSLOW BLACK M 202206 78 67 ERIOSLOW BLACK M 202206 14 58 ERIOSLOW BLACK M 202206 72 15 ERIOSLOW BLACK M 202206 76 50 ERIOSLOW YELLOW R 202206 17 35 ERIOSLOW BLACK M 202206 15 17 ERIOSLOW YELLOW R 202206 93 82 ERIOSLOW BLACK M 202206 68 11 ERIOSLOW BLACK M 202206 59 51 ERIOSLOW BLACK M 202206 91 27 ERIOSLOW BLACK M 202206 62 18 ERIOSLOW BLACK M 202206 46 12 ERIOSLOW BLACK M 202206 66 94 ERIOSLOW BLACK M 202206 81 11 ERIOSLOW BLACK M 202206 50 84 ERIOSLOW BLACK M 202206 69 37 ERIOSLOW BLACK M 202206 73 18 ERIOSLOW BLACK M 202207 84 24 ERIOSLOW BLACK M 202207 79 2 ERIOSLOW BLACK M 202207 75 72 ERIOSLOW BLACK M 202207 70 53 ERIOSLOW BLACK M 202207 83 64 ERIOSLOW BLACK M 202207 25 4 ERIOSLOW BLACK M 202207 94 43 ERIOSLOW BLACK M 202206 13 69 ERIOSLOW YELLOW R 202207 42 55 ERIOSLOW BLACK M 202207 53 76 ERIOSLOW BLACK M 202207 82 49 ERIOSLOW BLACK M 202207 15 3 ERIOSLOW BLACK M 202206 62 86 ERIOSLOW RED 2B 202207 83 89 ERIOSLOW BLACK M 202207 37 69 ERIOSLOW BLACK M 202207 79 26 ERIOSLOW BLACK M 202207 59 28 ERIOSLOW BLACK M 202206 41 30 ERIOSLOW RED 2B 202207 27 37 ERIOSLOW BLACK M 202206 34 59 ERIOSLOW RED 2B 202207 14 29 ERIOSLOW BLACK M 202207 8 65 ERIOSLOW BLACK M 202207 69 18 ERIOSLOW BLACK M 202207 23 56 ERIOSLOW BLACK M 202207 5 55 ERIOSLOW BLACK M 202207 91 69 ERIOSLOW BLACK M 202207 6 58 ERIOSLOW BLACK M 202207 63 58 ERIOSLOW BLACK M 202207 0 90 ERIOSLOW BLACK M 202207 60 57 ERIOSLOW BLACK M 202207 0 43 ERIOSLOW BLACK M 202207 81 31 ERIOSLOW BLACK M 202207 69 94 ERIOSLOW BLACK M 202207 70 29 ERIOSLOW BLACK M 202207 81 59 ERIOSLOW BLACK M 202207 82 92 ERIOSLOW BLACK M 202207 66 24 ERIOSLOW BLACK M 202207 60 29 ERIOSLOW BLACK M 202207 84 42 ERIOSLOW BLACK M 202207 34 97 ERIOSLOW BLACK M 202207 90 55 ERIOSLOW BLACK M 202207 42 66 ERIOSLOW BLACK M 202207 38 19 ERIOSLOW BLACK M 202207 91 22 ERIOSLOW BLACK M 202207 99 14 ERIOSLOW BLACK M 202207 83 68 ERIOSLOW BLACK M 202206 1 48 ERIOSLOW BLUE 3G 202207 66 66 ERIOSLOW BLACK M 202207 70 48 ERIOSLOW BLACK M 202207 60 2 ERIOSLOW BLACK M 202207 29 18 ERIOSLOW BLACK M 202207 35 68 ERIOSLOW BLACK M 202207 67 80 ERIOSLOW BLACK M 202206 40 87 ERIOSLOW BLACK M 202206 1 47 ERIOSLOW BLACK M 202206 53 22 ERIOSLOW BLACK M 202207 90 15 ERIOSLOW BLACK M 202206 70 12 ERIOSLOW BLUE 3G 202206 36 66 ERIOSLOW BLUE 3G 202206 28 26 ERIOSLOW BLUE 3G 202206 91 38 ERIOSLOW BLUE 3G 202206 63 94 ERIOSLOW BLUE 3G 202206 60 19 ERIOSLOW BLUE 3G 202206 57 22 ERIOSLOW BLUE 3G 202206 76 58 ERIOSLOW BLUE 3G 202206 75 99 ERIOSLOW BLUE 3G 202206 49 42 ERIOSLOW BLUE 3G 202206 68 83 ERIOSLOW BLUE 3G 202206 18 14 ERIOSLOW BLUE 3G 202206 67 88 ERIOSLOW BLUE 3G 202206 4 22 ERIOSLOW BLUE 3G 202206 24 70 ERIOSLOW BLUE 3G 202206 56 27 ERIOSLOW BLUE 3G 202206 54 19 ERIOSLOW BLUE 3G 202206 49 76 ERIOSLOW BLUE 3G 202206 87 10 ERIOSLOW BLUE 3G 202207 14 60 ERIOSLOW BLUE 3G 202207 61 68 ERIOSLOW BLUE 3G 202207 50 6 ERIOSLOW BLUE 3G 202206 55 66 ERIOSLOW YELLOW 5G 202207 21 48 ERIOSLOW BLUE 3G 202207 47 59 ERIOSLOW BLUE 3G 202207 11 6 ERIOSLOW BLUE 3G 202207 71 32 ERIOSLOW BLUE 3G 202207 65 58 ERIOSLOW BLUE 3G 202207 11 43 ERIOSLOW BLUE 3G 202207 70 68 ERIOSLOW BLUE 3G 202207 68 58 ERIOSLOW BLUE 3G 202207 59 15 ERIOSLOW BLUE 3G 202207 49 85 ERIOSLOW BLUE 3G 202207 84 30 ERIOSLOW BLUE 3G 202207 63 43 ERIOSLOW BLUE 3G 202207 98 30 ERIOSLOW BLUE 3G 202207 80 98 ERIOSLOW BLUE 3G 202207 76 23 ERIOSLOW BLUE 3G 202207 86 87 ERIOSLOW BLUE 3G 202207 59 98 ERIOSLOW BLUE 3G 202207 67 63 ERIOSLOW BLUE 3G 202207 62 60 ERIOSLOW BLUE 3G 202207 29 73 ERIOSLOW BLUE 3G 202207 32 11 ERIOSLOW BLUE 3G 202207 35 62 ERIOSLOW BLUE 3G 202206 0 86 ERIOSLOW BLUE 3R 202206 68 25 ERIOSLOW BLUE 3R 202206 69 45 ERIOSLOW BLUE 3R 202206 88 33 ERIOSLOW BLUE 3R 202206 91 20 ERIOSLOW BLACK M 202206 51 66 ERIOSLOW BLUE 3R 202206 91 66 ERIOSLOW BLUE 3R 202206 99 50 ERIOSLOW BLACK M 202206 25 35 ERIOSLOW BLACK M 202206 81 18 ERIOSLOW BLUE 3R 202206 35 52 ERIOSLOW BLACK M 202206 69 7 ERIOSLOW BLUE 3R 202206 45 51 ERIOSLOW NAVY M 202206 9 95 ERIOSLOW BLUE 3R 202206 35 28 ERIOSLOW BLUE 3R 202206 69 90 ERIOSLOW BLUE 3R 202206 15 21 ERIOSLOW BLUE 3R 202206 63 40 ERIOSLOW BLUE 3R 202206 85 0 ERIOSLOW BLUE 3R 202206 15 47 ERIOSLOW BLUE 3R 202206 52 62 ERIOSLOW BLUE 3R 202206 13 53 ERIOSLOW BLUE 3R 202206 29 47 ERIOSLOW BLUE 3R 202206 72 50 ERIOSLOW BLUE 3R 202206 89 1 ERIOSLOW BLUE 3R 202206 14 81 ERIOSLOW BLUE 3R 202206 59 73 ERIOSLOW BLUE 3R 202206 95 67 ERIOSLOW NAVY M 202206 97 99 ERIOSLOW BLUE 3R 202206 27 69 ERIOSLOW BLUE 3R 202206 13 25 ERIOSLOW BLUE 3R 202206 65 66 ERIOSLOW BLUE 3R 202206 50 93 ERIOSLOW BLUE 3R 202206 56 45 ERIOSLOW BLUE 3R 202206 66 45 ERIOSLOW BLUE 3R 202206 11 28 ERIOSLOW BLUE 3R 202207 94 82 ERIOSLOW BLUE 3R 202207 69 46 ERIOSLOW BLUE 3R 202207 79 66 ERIOSLOW BLUE 3R 202207 79 97 ERIOSLOW BLUE 3R 202207 1 16 ERIOSLOW BLUE 3R 202207 30 37 ERIOSLOW BLUE 3R 202207 12 40 ERIOSLOW BLUE 3R 202207 28 19 ERIOSLOW BLUE 3R 202207 5 30 ERIOSLOW BLUE 3R 202207 51 1 ERIOSLOW BLUE 3R 202207 96 57 ERIOSLOW BLUE 3R 202207 20 75 ERIOSLOW BLUE 3R 202207 100 0 ERIOSLOW BLUE 3R 202207 39 4 ERIOSLOW BLUE 3R 202207 85 70 ERIOSLOW BLUE 3R 202207 48 42 ERIOSLOW BLUE 3R 202207 86 60 ERIOSLOW BLUE 3R 202207 75 31 ERIOSLOW BLUE 3R 202207 93 50 ERIOSLOW BLUE 3R 202207 90 37 ERIOSLOW BLUE 3R 202207 88 34 ERIOSLOW BLUE 3R 202207 74 38 ERIOSLOW BLUE 3R 202207 49 84 ERIOSLOW BLUE 3R 202207 65 54 ERIOSLOW BLUE 3R 202207 48 99 ERIOSLOW BLUE 3R 202207 70 87 ERIOSLOW BLUE 3R 202207 70 20 ERIOSLOW BLUE 3R 202207 55 21 ERIOSLOW BLUE 3R 202207 1 64 ERIOSLOW BLUE 3R 202207 99 64 ERIOSLOW BLUE 3R 202207 3 3 ERIOSLOW BLUE 3R 202207 53 40 ERIOSLOW BLUE 3R 202207 31 19 ERIOSLOW BLUE 3R 202207 81 3 ERIOSLOW BLUE 3R 202207 82 2 ERIOSLOW BLUE 3R 202207 100 8 ERIOSLOW BLUE 3R 202207 30 76 ERIOSLOW BLUE 3R 202207 18 23 ERIOSLOW BLUE 3R 202206 78 98 ERIOSLOW BLUE RHK 600 202207 18 98 ERIOSLOW BLUE RHK 600 202206 26 24 ERIOSLOW BRILLIANT RED 3G 202206 64 62 ERIOSLOW BRILLIANT RED 3G 202206 43 92 ERIOSLOW BRILLIANT RED 3G 202206 60 33 ERIOSLOW BRILLIANT RED 3G 202206 46 61 ERIOSLOW BRILLIANT RED 3G 202206 90 98 ERIOSLOW BRILLIANT RED 3G 202206 48 18 ERIOSLOW BRILLIANT RED 3G 202206 20 27 ERIOSLOW BRILLIANT RED 3G 202206 34 87 ERIOSLOW BRILLIANT RED 3G 202206 41 31 ERIOSLOW BRILLIANT RED 3G 202207 10 10 ERIOSLOW BRILLIANT RED 3G 202207 70 99 ERIOSLOW BRILLIANT RED 3G 202207 96 68 ERIOSLOW BRILLIANT RED 3G 202207 17 3 ERIOSLOW BRILLIANT RED 3G 202207 55 86 ERIOSLOW BRILLIANT RED 3G 202207 5 73 ERIOSLOW BRILLIANT RED 3G 202207 34 37 ERIOSLOW BRILLIANT RED 3G 202207 50 57 ERIOSLOW BRILLIANT RED 3G 202207 11 90 ERIOSLOW BRILLIANT RED 3G 202207 3 100 ERIOSLOW BRILLIANT RED 3G 202207 62 76 ERIOSLOW BRILLIANT RED 3G 202207 12 94 ERIOSLOW BRILLIANT RED 3G 202207 19 86 ERIOSLOW BRILLIANT RED 3G 202207 20 42 ERIOSLOW BRILLIANT RED 3G 202206 86 46 ERIOSLOW DEEP BLACK RHK-2000
Group Name Product ECLASS ECLASS RED ECLASS ECLASS GREEN ECLASS ECLASS YELLOW ECLASS ECLASS WHITE NEON NEON BLUE NEON NEON VIOLET NEON NEON PURPLE NEON NEON INDIGO LASTVOL LASTVOL PINK LASTVOL LASTVOL BLACK LASTVOL LASTVOL LIME LASTVOL LASTVOL BROWN 202206 202206 202206 202207 202207 202207 202208 202208 202208 Product Name WD10 June VS May WD10-June WD10-May WD10 June VS May WD10-June WD10-May WD10 June VS May WD10-June WD10-May NEON BLUE 1,376 5,639 4,263 806 5,318 4,512 789 5,481 4,692 ***NEON BLUE is > 0, then NEON INDIGO should also be highlighted with same colour (YELLOW), as they both belong to SAME Group of NEON NEON BLUE (31,543) 41,183 72,726 (37,455) 28,624 66,079 (37,777) 33,949 71,727 ALBA STRING (881) 708 1,589 (1,273) 596 1,869 (1,485) 661 2,146 LASTVOL PINK - 1,167 1,167 - 1,319 1,319 - 1,656 1,656 LASTVOL BLACK (27) 648 675 (46) 514 560 (46) 536 582 NEON INDIGO 149 1,404 1,255 (67) 1,343 1,410 (97) 1,404 1,501 NEON INDIGO 789 5,196 4,407 (186) 5,071 5,257 (470) 5,637 6,107 ALBA SUMMER - - - - - - - - - LASTVOL LIME - 1,020 1,020 (357) 883 1,240 (370) 820 1,190 LASTVOL BROWN (314) 16,485 16,799 19 24,649 24,631 (890) 14,851 15,742 NEON PURPLE (27) 3,526 3,553 260 3,757 3,497 314 3,281 2,967 Grand Total (30,477) 76,976 107,454 (38,299) 72,074 110,374 (40,032) 68,276 108,309 Same for LASTVOL Your second table is not in a format that can be used. Power Query doesn't know what to do with multiple headers. Can the first two rows be combined?
This is what a usable format would look like:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZTBbsIwDIZfpeLsSbHjOMkRBkIdpSAY44A4oG3HbdI0Dnv7FaSJxmYTkXr5+tX5naTZ7QbLz4+X4/NX1R7eXgcwIEfkpNqO0d09HN81mh++f0m0UjRSslK6SHvYDdrJoq1GzWbSva8uI4D4XCIGEq8tj0lbAUlbnFBbkunv+RkBUzFXJIgkfUIJhLhPRMDFIrT3kLkgEbs68TzzsBkNq/Xjqm6nZbiT5lRbCCFlo4UsWktiNRHVPgGynEM0w/Xj06KplnU706VQ4g3IY74BSTBJT6gfYdQM72c2OyfLYrBLgWyZOMu8WJZ6Z6Fux/V0ocOyY40oBNM4e/MhuhtqBYf/JwiAeq+5KxS15SJqRMFY4hUSQNc/lZv5fLLqFHf16e9aU88nuhtHpmeDupGSWS3iK5qthrkMMVottm1fQun++aBIzMWxpO4SYEN8sX7IkEJJAkTuHZflZrVs1Ap4CCQGBa9R1DvjgbNBpC8vgtz9hvv9Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column3 = _t, Column4 = _t, Column6 = _t, Column7 = _t, Column9 = _t, Column10 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Product Name"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}}) in #"Changed Type"Yes Ibendlin, Thank you for your reply. Yes , Thank you for the suggestion. I wanted to upload the file as attachement in excel but seems there is no option for that.
Please can you help me on how to get the product in one specific group highlighted when diff > 0. Is there a DAX function for the same. Thank you
