Create slicer groups by selecting columns

[Anonymous]

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.

[lbendlin]

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.

[Anonymous]

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:

[lbendlin · Accepted solution]

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