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
Buenos días
Estoy tratando de crear un informe para analizar datos de chatarra en mi planta de fabricación. Me gusta comenzar a un alto nivel y ser capaz de explorar en profundidad para problemas específicos. A continuación se muestra un ejemplo de los datos.
La fabricación se divide en diferentes departamentos. La impresión sería una de ellas. Ese departamento definirá chatarra en términos de categorías como Dirt, Scratches, Color, etc. Todo eso se agregará a una chatarra de departamento general para impresión. Esto se hace para todos los departamentos.
Me gustaría tener un gráfico visual de gráfico de columnas agrupadas que muestra los totales de chatarra para cada departamento. Así que tendría una columna para Imprimir chatarra. Me gustaría hacer clic en eso y ser capaz de profundizar en las subcategorías de suciedad, arañazos y color para ver el desglose.
Si esto no es posible, tener otro gráfico de columnas agrupadas en la misma página que muestre las subcategorías respectivas cuando se hace clic en la columna de rechazo del departamento también funcionaría.
Gracias por el tiempo y el apoyo de todos de antemano.
Fecha | Orden de trabajo | Lote | Impresión de chatarra (total) | Suciedad | Arañazos | 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 |
Hola @hvora787
¿Este problema es amado?
Si es amado, ¿podría aceptarlo amablemente como una solución para cerrar este caso y ayudar a los otros miembros a encontrarlo más rápidamente?
Si no, por favor no dude en ha decirmelo.
Saludos
Maggie
Hola @hvora787
¿La tabla muestra la estructura de sus datos reales?
Si no, ¿podría mostrar más información?
Saludos
Maggie
Hola Maggie,
He pegado algunos de los datos a continuación. No estoy familiarizado con la interfase para esta placa y no pude encontrar una manera de adjuntar el archivo de Excel real. Por favor, hágamelo saber si esto funciona para usted.
R, N, I, C, M y P Los rechazos son chatarra de diferentes departamentos. R R1, R R2 y así sucesivamente en el lado derecho de la tabla son subcategoríaes de rechazos de cada departamento. Por favor, ignore la fila superior. Cuando se elimina el formato HTML, las celdas superiores no se descomponen.
Por favor, hágamelo saber si tiene alguna pregunta. Gracias por su ayuda.
saludos
Hemal
Detalle del pedido | Departamentos | Rendimiento | DEPARTAMENTO SUB-CATAGORIES | |||||||||||||||||||||||||
Número de trabajo | Orden de trabajo | Lote | Fecha | R Rechazos | N Rechazos | Rechazo | C Rechazos | M Rechazos | P Rechazos | bien | Muestras de control de calidad | Total Malo | Tamaño de lote | Rendimiento | % de chatarra | 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 |
Hola @hvora787
Si desea mostrar subcategoría en categoría al profundizar, consulte mi consulta a continuación:
Tabla2
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)"
Tabla 2(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"
Saludos
Maggie
Equipo de apoyo a la comunidad _ Maggie Li
Si este post ayuda, entonces considera Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.
Hola Maggie,
Gracias por proporcionar la respuesta detallada. Esto es de gran ayuda.
saludos
Hemal
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.