Forum Discussion
Create slicer groups by selecting columns
Hello,
I have the following table and i am trying to present the data in a stacked column visualization with grouping selectable in a slicer based on specific columns.
The X axis is the week number and the Y axis shows the number of items according to each category.
The 3 groupings should be:
1. Warehouse: Stock and Returns
2. Dock: Defective and Sold
3. Transport: Excess and Expired.
These 3 groupings should be in a slicer, so when "Warehouse" is selected, it should filter out the chart to only show Stock and Returns. Is this possible? Any help is much appreciated!
| Year | Week number | Stock | Returns | Defective | Sold | Excess | Expired |
| 2022 | 1 | 3 | 4 | 8 | 2 | 8 | 11 |
| 2022 | 2 | 6 | 9 | 17 | 6 | 2 | 10 |
| 2022 | 3 | 2 | 5 | 10 | 3 | 6 | 12 |
| 2022 | 4 | 9 | 3 | 16 | 3 | 5 | 12 |
| 2022 | 5 | 5 | 6 | 15 | 7 | 3 | 15 |
| 2022 | 6 | 2 | 2 | 10 | 2 | 3 | 11 |
| 2022 | 7 | 7 | 8 | 22 | 9 | 3 | 19 |
| 2022 | 8 | 3 | 3 | 13 | 4 | 7 | 13 |
| 2022 | 9 | 6 | 3 | 18 | 7 | 2 | 18 |
| 2022 | 10 | 3 | 3 | 14 | 8 | 9 | 14 |
| 2022 | 11 | 2 | 7 | 17 | 5 | 2 | 17 |
| 2022 | 12 | 3 | 7 | 22 | 3 | 6 | 21 |
The chart with the data from the table:
I have tried to make a group from the Fields area but it is not working. It should allow selecting the column names instead of showing the values in that column.
For that to work you need to change your data model. Unpivot the source data and add the categories there.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZDNDoIwDMdfxezMgVXn4K4voAdjCAeFmhAUDaDx8e22Ninx8M/68WvXtqrMGS+jycwJsV8N78cVg3ecn01P7wHn9zhMZO3whs3cfTBkn/eWnv23wWmKxqsbsTV1VhnIAShkSWvShlSQgF9rNRW0JZUh49mJ5bnG1hx1KZMCAbWgsQ13Clm7ZcP9YY4VGwTDS43TmIwC8qlMslzBs+KSoCcoNVZwNGbkMD45Civ544gVjEBy9HVz3U5uXCZHc5bLvRzYST+/4GQ3L1vIhYGWrX8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Year", "Week number"}, "Channel", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Category", each if List.Contains({"Stock","Returns"},[Channel]) then "Warehouse" else if List.Contains({"Defective","Sold"},[Channel]) then "Dock" else "Transport"), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Category", type text}, {"Value", Int64.Type}}) in #"Changed Type"see new version attached
3 Replies
Instead of the three groups you need to create three measures. If you want you can also create a disconnected table with these three choices and then tie the measure result to the selected value. Or use the fancy new Field Parameters.
see attached.
Hi lbendlin Thanks for your reply! The new Field Parameters escaped me! But is it possible to show the categories for each grouping in a legend?
For example, when "Warehouse" is selected, the graph should look as follows:
For that to work you need to change your data model. Unpivot the source data and add the categories there.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZDNDoIwDMdfxezMgVXn4K4voAdjCAeFmhAUDaDx8e22Ninx8M/68WvXtqrMGS+jycwJsV8N78cVg3ecn01P7wHn9zhMZO3whs3cfTBkn/eWnv23wWmKxqsbsTV1VhnIAShkSWvShlSQgF9rNRW0JZUh49mJ5bnG1hx1KZMCAbWgsQ13Clm7ZcP9YY4VGwTDS43TmIwC8qlMslzBs+KSoCcoNVZwNGbkMD45Civ544gVjEBy9HVz3U5uXCZHc5bLvRzYST+/4GQ3L1vIhYGWrX8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Year", "Week number"}, "Channel", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Category", each if List.Contains({"Stock","Returns"},[Channel]) then "Warehouse" else if List.Contains({"Defective","Sold"},[Channel]) then "Dock" else "Transport"), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Category", type text}, {"Value", Int64.Type}}) in #"Changed Type"see new version attached