Drill Down to Sub-Catagories for Clustered Column Chart

[hvora787]

Good Morning,

I am trying to create a report for analyzing scrap data in my manufacturing plant. I like to start at a high level and be able to drill down for specific issues. Below is an example of the data. 

Manufacturing is divided into different departments. Printing would be one of them. That department will define scrap in terms of categories such as Dirt, Scratches, Color, etc. That will all be added into an overall department scrap for Printing. This is done for all departments. 

I would like to have a visual of Clustered Column Chart that shows scrap totals for each department. So I would have a column for Printing Scrap. I would like to click on that and be able to Drill Down to the sub categories of Dirt, Scratches and Color to see the breakdown. 

If this is not possible, having another Clustered Column Chart on the same page that shows the respective sub categories when the department scrap column is clicked would also work. 

Thank you for everyone’s time and support in advance. 

Date	Work Order	Batch	Printing Scrap (Total)	Dirt	Scratches	Color
1/1/2020	123	1	8	1	2	5
1/2/2020	123	2	14	5	5	4
1/2/2020	567	1	18	8	3	7
1/3/2020	567	2	16	8	2	6

[v-juanli-msft]

Hi @hvora787 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @hvora787 

Is the table show the structure of your real data?

If not, could you show more information?

Best Regards

Maggie

[hvora787]

Hi Maggie,

I have pasted some of the data below. I am not not familiar with the interfase for this board and could not find a way to attach the actual Excel file. Please let me know if this works for you. 

R, N, I, C, M and P Rejects are scrap from different departments. R R1, R R2 and so on on the right side fo the table are sub-catagories of rejects from each departments. Please ignore the top row. When the HTML formatting is removed the top cells unmerged. 

Please let me know if you have any questions. Thank you for your help. 

Regards,

Hemal 

Order Detail	Departments	Yield	DEPARTMENT SUB-CATAGORIES
Job Number	Work Order	Batch	Date	R Rejects	N Rejects	I Rejects	C Rejects	M Rejects	P Rejects	Good	QC Samples	Total Bad	Batch Size	Yield	Scrap %	R R1	R R2	N R1	N R2	I R1	I R2	I R3	C R1	C R2	M R1	M R2	P R1	P R2
11823	1913600	682	1/2	9	7	70	6	11	11	382	2	116	500	76%	23%	5	4	5	2	24	4	42	1	5	1	10	1	10
11823	1913600	683	1/2	4	6	44	10	9	14	409	2	89	500	82%	18%	1	3	5	1	16	3	25	5	5	2	7	5	9
11823	1913600	685	1/2	9	5	97	3	20	11	351	2	147	500	70%	29%	5	4	4	1	44	28	25	2	1	8	12	8	3
11823	1913600	689	1/2	4	4	31	10	19	9	419	2	79	500	84%	16%	3	1	2	2	2	3	26	5	5	12	7	7	2
11823	1913600	717	1/2	3	7	75	4	9	20	378	2	120	500	76%	24%	2	1	4	3	31	29	15	3	1	3	6	10	10
12165	1914152	540	1/2	8	9	55	4	12	15	393	2	105	500	79%	21%	4	4	4	5	1	31	23	3	1	10	2	9	6
12165	1914152	544	1/2	3	5	45	5	5	16	405	8	87	500	81%	17%	2	1	3	2	17	9	19	1	4	1	4	9	7
12165	1914152	567	1/2	10	8	113	4	19	6	336	2	162	500	67%	32%	5	5	5	3	40	50	23	2	2	10	9	2	4
12165	1914152	574	1/2	6	7	117	9	8	11	338	2	160	500	68%	32%	2	4	2	5	50	37	30	4	5	2	6	3	8
12165	1914152	585	1/2	2	6	65	3	11	8	401	2	97	500	80%	19%	1	1	5	1	20	15	30	1	2	2	9	5	3
12165	1914152	586	1/2	7	7	33	7	20	21	401	2	97	500	80%	19%	3	4	5	2	13	9	11	5	2	9	11	10	11
12165	1914152	591	1/2	6	6	102	7	12	9	354	2	144	500	71%	29%	4	2	2	4	39	36	27	5	2	8	4	5	4
12165	1914152	594	1/2	6	7	36	5	4	16	422	2	76	500	84%	15%	5	1	3	4	3	28	5	1	4	3	1	9	7
12165	1914152	595	1/2	6	5	100	6	7	3	369	2	129	500	74%	26%	1	5	4	1	2	50	48	4	2	3	4	2	1
12165	1914152	597	1/2	10	8	73	6	14	8	377	2	121	500	75%	24%	5	5	5	3	30	17	26	1	5	12	2	3	5

[v-juanli-msft · Accepted solution]

Hi @hvora787 

If you want to show sub-category under category when drilling down, please refer to my query below:

Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZVLjhsxDETv0oB3E0AU9b1CrjDwIhfI/ZexSiyJbliYLNTd1vexRJa/vy+RFvX6uqSLlhBeX6XF1zP++v3n7+vdX62OhqExUfjQORE/x0jG8loeo1fHM79asjfmJutIWGUj2DDsj+fXZyx1WMloUuLiASrYO3Q7rvVF1eLgkfawY9SfXawjZuslb7XvfmTKN6kwu3K/sKTKQqlS3VIFSNW9VMmgEFhspKJeo0OifegRq9+kGk23zt1ok1Cq6qRKEAnXqHZsdA2RFaeUUKqKGZ+ZqlTHpFzBsDvl0toYLn6/JRXIKEWyfRBXxPVnh6yWITNiy6ooJU+sJHlslFNwWI23SCyENrftyrND3ly4uygPpzMTXhabOizgRDunHKnSTSwA+fRE0iagDOq2s6qBRqrXarFXOxkXTxX5nrV+QCr+/hAFclGUm3QTXLXwtBIXVQGPRuY6G5bPi6ZUy1VY1hFHHLiql6pYXskKdEICayVW2YlV2saKFkkk3szH8QjXu5HRMdqJ6s0YuKKs/GQpp8Dq6u4CYQvS6VXeJ6elZDL52qT96JGpOCbWq7IUsXOU/4HilS9jUmaVuN7VIWaDB6wutwucVUtTEe6lmZcjKA+rQNkeynFepGIZoq4OrDn8Y1r1T2mlZS1jBUYeWPdfoDloZq6zAulYsHUOsFOuHwqw5xsRxkPwfGBkyUjcpl6ndRbm1ArC5iLXE5WhzS/Bj0wfTaEu703Wo7UuKNlQebv63RNmemNVcczi/4Xy9Xz+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Number" = _t, #"Work Order" = _t, Batch = _t, Date = _t, #"R Rejects" = _t, #"N Rejects" = _t, #"I Rejects" = _t, #"C Rejects" = _t, #"M Rejects" = _t, #"P Rejects" = _t, Good = _t, #"QC Samples" = _t, #"Total Bad" = _t, #"Batch Size" = _t, Yield = _t, #"Scrap %" = _t, #"R R1" = _t, #"R R2" = _t, #"N R1" = _t, #"N R2" = _t, #"I R1" = _t, #"I R2" = _t, #"I R3" = _t, #"C R1" = _t, #"C R2" = _t, #"M R1" = _t, #"M R2" = _t, #"P R1" = _t, #"P R2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Number", Int64.Type}, {"Work Order", Int64.Type}, {"Batch", Int64.Type}, {"Date", type date}, {"R Rejects", Int64.Type}, {"N Rejects", Int64.Type}, {"I Rejects", Int64.Type}, {"C Rejects", Int64.Type}, {"M Rejects", Int64.Type}, {"P Rejects", Int64.Type}, {"Good", Int64.Type}, {"QC Samples", Int64.Type}, {"Total Bad", Int64.Type}, {"Batch Size", Int64.Type}, {"Yield", Percentage.Type}, {"Scrap %", Percentage.Type}, {"R R1", Int64.Type}, {"R R2", Int64.Type}, {"N R1", Int64.Type}, {"N R2", Int64.Type}, {"I R1", Int64.Type}, {"I R2", Int64.Type}, {"I R3", Int64.Type}, {"C R1", Int64.Type}, {"C R2", Int64.Type}, {"M R1", Int64.Type}, {"M R2", Int64.Type}, {"P R1", Int64.Type}, {"P R2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"R Rejects", "N Rejects", "I Rejects", "C Rejects", "M Rejects", "P Rejects"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Rejects"}, {"Attribute.2", "sub_Rejects"}, {"Value", "sub_Rejects value"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %", "Rejects"}, #"Table 2 (2)", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %", "Rejects"}, "Table 2 (2)", JoinKind.FullOuter),
    #"Expanded Table 2 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table 2 (2)", {"Rejects value"}, {"Table 2 (2).Rejects value"})
in
    #"Expanded Table 2 (2)"

Table2(2)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZVLjhsxDETv0oB3E0AU9b1CrjDwIhfI/ZexSiyJbliYLNTd1vexRJa/vy+RFvX6uqSLlhBeX6XF1zP++v3n7+vdX62OhqExUfjQORE/x0jG8loeo1fHM79asjfmJutIWGUj2DDsj+fXZyx1WMloUuLiASrYO3Q7rvVF1eLgkfawY9SfXawjZuslb7XvfmTKN6kwu3K/sKTKQqlS3VIFSNW9VMmgEFhspKJeo0OifegRq9+kGk23zt1ok1Cq6qRKEAnXqHZsdA2RFaeUUKqKGZ+ZqlTHpFzBsDvl0toYLn6/JRXIKEWyfRBXxPVnh6yWITNiy6ooJU+sJHlslFNwWI23SCyENrftyrND3ly4uygPpzMTXhabOizgRDunHKnSTSwA+fRE0iagDOq2s6qBRqrXarFXOxkXTxX5nrV+QCr+/hAFclGUm3QTXLXwtBIXVQGPRuY6G5bPi6ZUy1VY1hFHHLiql6pYXskKdEICayVW2YlV2saKFkkk3szH8QjXu5HRMdqJ6s0YuKKs/GQpp8Dq6u4CYQvS6VXeJ6elZDL52qT96JGpOCbWq7IUsXOU/4HilS9jUmaVuN7VIWaDB6wutwucVUtTEe6lmZcjKA+rQNkeynFepGIZoq4OrDn8Y1r1T2mlZS1jBUYeWPdfoDloZq6zAulYsHUOsFOuHwqw5xsRxkPwfGBkyUjcpl6ndRbm1ArC5iLXE5WhzS/Bj0wfTaEu703Wo7UuKNlQebv63RNmemNVcczi/4Xy9Xz+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Number" = _t, #"Work Order" = _t, Batch = _t, Date = _t, #"R Rejects" = _t, #"N Rejects" = _t, #"I Rejects" = _t, #"C Rejects" = _t, #"M Rejects" = _t, #"P Rejects" = _t, Good = _t, #"QC Samples" = _t, #"Total Bad" = _t, #"Batch Size" = _t, Yield = _t, #"Scrap %" = _t, #"R R1" = _t, #"R R2" = _t, #"N R1" = _t, #"N R2" = _t, #"I R1" = _t, #"I R2" = _t, #"I R3" = _t, #"C R1" = _t, #"C R2" = _t, #"M R1" = _t, #"M R2" = _t, #"P R1" = _t, #"P R2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Number", Int64.Type}, {"Work Order", Int64.Type}, {"Batch", Int64.Type}, {"Date", type date}, {"R Rejects", Int64.Type}, {"N Rejects", Int64.Type}, {"I Rejects", Int64.Type}, {"C Rejects", Int64.Type}, {"M Rejects", Int64.Type}, {"P Rejects", Int64.Type}, {"Good", Int64.Type}, {"QC Samples", Int64.Type}, {"Total Bad", Int64.Type}, {"Batch Size", Int64.Type}, {"Yield", Percentage.Type}, {"Scrap %", Percentage.Type}, {"R R1", Int64.Type}, {"R R2", Int64.Type}, {"N R1", Int64.Type}, {"N R2", Int64.Type}, {"I R1", Int64.Type}, {"I R2", Int64.Type}, {"I R3", Int64.Type}, {"C R1", Int64.Type}, {"C R2", Int64.Type}, {"M R1", Int64.Type}, {"M R2", Int64.Type}, {"P R1", Int64.Type}, {"P R2", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Job Number", "Work Order", "Batch", "Date", "R Rejects", "N Rejects", "I Rejects", "C Rejects", "M Rejects", "P Rejects", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Job Number", "Work Order", "Batch", "Date", "Good", "QC Samples", "Total Bad", "Batch Size", "Yield", "Scrap %"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Columns", {{"Attribute", each Text.BeforeDelimiter(_, " "), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Attribute", "Rejects"}, {"Value", "Rejects value"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Rejects", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hvora787]

Hi Maggie,

Thank you for providing the detailed response. This is of great help.

Regards,

Hemal