- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Descuadre de errores
Buenos días,
Estoy haciendo un Power BI que muestra el avance de la resolución de errores que tienen varias personas. Estos errores los actualizo mes a mes. Lo que quiero es que, si los errores aumentan un porcentaje con respecto al mes anterior, aparezca.
Es decir: Si la suma de los errores del mes actual, es un 10 % mayor a la suma de errores del mes pasado, que aparezca el nombre del responsable.
Saludos y muchas gracias
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi again,
Result
You can delete these steps:
but in CombinedTables step, you should specify your tables.
Whole code with sample data:
let
fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
//v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
let
a = Table.Column(tbl, col),
b = if shift = 0 or shift = null then a else if shift > 0
then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),
c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
( if newColName <> null then {newColName} else
if shift = 0 then {col & "_Duplicate"} else
if shift > 0 then {col & "_PrevValue"}
else {col & "_NextValue"} )),
d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
in
d,
TableFebruary = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFPSUTICYkMobWCob2Ckb2RgZKIUqxOt5JiTlFpUkg9VYACjURV55RenIsmiq4gFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nombre = _t, Pinchazos = _t, Motor = _t, Chapa = _t, Fecha = _t]),
TableMarch = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFPSUTIEYgMgNgLRhvoGxvpGBkYmSrE60UqOOUmpRSX5UEUgbIKpyCu/OBXJGEM0FbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nombre = _t, Pinchazos = _t, Motor = _t, Chapa = _t, Fecha = _t]),
ChangedTypeFeb = Table.TransformColumnTypes(TableFebruary,{{"Nombre", type text}, {"Pinchazos", Int64.Type}, {"Motor", Int64.Type}, {"Chapa", Int64.Type}, {"Fecha", type date}}),
ChangedTypeMarch = Table.TransformColumnTypes(TableMarch,{{"Nombre", type text}, {"Pinchazos", Int64.Type}, {"Motor", Int64.Type}, {"Chapa", Int64.Type}, {"Fecha", type date}}),
CombinedTables = Table.Combine({ ChangedTypeFeb, ChangedTypeMarch }),
UnpivotedOtherColumns = Table.UnpivotOtherColumns(CombinedTables, {"Nombre", "Fecha"}, "Error Type", "Value"),
ChangedFechaFormat = Table.TransformColumns(UnpivotedOtherColumns, {{"Fecha", each Date.ToText(_, "yyyy-MM", "es-ES"), type text}}),
GroupedRows = Table.Group(ChangedFechaFormat, {"Nombre"}, {{"All", each
[ a = Table.Group(Table.Sort(_, {{"Fecha", Order.Ascending}}), {"Fecha"}, {{"Errors", each List.Sum([Value]), Int64.Type}}), //Sort + inner group
b = fnShift(a, "Errors", 1, null, Int64.Type), //shift rows - add prev. value
c = Table.FromColumns(Table.ToColumns(#table(null, List.Repeat({{[Nombre]{0}?}}, 2))) & Table.ToColumns(b), Value.Type(#table(type table[Nombre=text], {}) & b))
][c], type table}}),
CombinedAll = Table.Combine(GroupedRows[All]),
FilteredRows = Table.SelectRows(CombinedAll, each ([Errors_PrevValue] <> null)),
Ad_VsPrevMonth = Table.AddColumn(FilteredRows, "VS prev. Month", each Percentage.From([Errors] / [Errors_PrevValue]), Percentage.Type),
FilteredRows2 = Table.SelectRows(Ad_VsPrevMonth, each [VS prev. Month] >= 1.1)
in
FilteredRows2
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Buenos días,
Añado algunos datos de muestra:
Tabla de Febrero
Nombre | Pinchazos | Motor | Chapa | Fecha |
Juan | 2 | 1 | 2 | 01/02/2024 |
Alberto | 1 | 0 | 1 | 01/02/2024 |
Jose | 0 | 1 | 1 | 01/02/2024 |
Tabla de Marzo
Nombre | Pinchazos | Motor | Chapa | Fecha |
Juan | 1 | 0 | 2 | 01/03/2024 |
Alberto | 1 | 1 | 4 | 01/03/2024 |
Jose | 1 | 0 | 1 | 01/03/2024 |
Quiero sumar todos los errores de Juan, Alberto y Jose y comprobar si el mes siguiente ha aumentado un 10% los errores.
Por ejemplo: Cogemos a Juan, que la suma de los errores de Febrero es 5 (2+1+2), le suamos un 10% y la comparamos con la suma de errores de Marzo que es 3 (1+0+2). Es decir, 5X10/100=0,5--5+0,5=5,5>3. En este caso Juan no aparecería porque en Marzo ha bajado los errores.
Por otro lado, Alberto=Errores Febrero+10% es menor que Errores Marzo--1+0+1=2+2x10/100=2,2<(1+1+4)=2,2<6. Por lo que Alberto si debería aparecer.
Y con el caso de Jose=Errores Febrero+10%<Errores Marzo--(0+1+1)+2x10/100<1+0+1=2+0,2<2=2,2<2. Por lo que Jose no debería aparecer
Saludos y gracias
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi again,
Result
You can delete these steps:
but in CombinedTables step, you should specify your tables.
Whole code with sample data:
let
fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
//v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
let
a = Table.Column(tbl, col),
b = if shift = 0 or shift = null then a else if shift > 0
then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),
c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
( if newColName <> null then {newColName} else
if shift = 0 then {col & "_Duplicate"} else
if shift > 0 then {col & "_PrevValue"}
else {col & "_NextValue"} )),
d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
in
d,
TableFebruary = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFPSUTICYkMobWCob2Ckb2RgZKIUqxOt5JiTlFpUkg9VYACjURV55RenIsmiq4gFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nombre = _t, Pinchazos = _t, Motor = _t, Chapa = _t, Fecha = _t]),
TableMarch = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFPSUTIEYgMgNgLRhvoGxvpGBkYmSrE60UqOOUmpRSX5UEUgbIKpyCu/OBXJGEM0FbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nombre = _t, Pinchazos = _t, Motor = _t, Chapa = _t, Fecha = _t]),
ChangedTypeFeb = Table.TransformColumnTypes(TableFebruary,{{"Nombre", type text}, {"Pinchazos", Int64.Type}, {"Motor", Int64.Type}, {"Chapa", Int64.Type}, {"Fecha", type date}}),
ChangedTypeMarch = Table.TransformColumnTypes(TableMarch,{{"Nombre", type text}, {"Pinchazos", Int64.Type}, {"Motor", Int64.Type}, {"Chapa", Int64.Type}, {"Fecha", type date}}),
CombinedTables = Table.Combine({ ChangedTypeFeb, ChangedTypeMarch }),
UnpivotedOtherColumns = Table.UnpivotOtherColumns(CombinedTables, {"Nombre", "Fecha"}, "Error Type", "Value"),
ChangedFechaFormat = Table.TransformColumns(UnpivotedOtherColumns, {{"Fecha", each Date.ToText(_, "yyyy-MM", "es-ES"), type text}}),
GroupedRows = Table.Group(ChangedFechaFormat, {"Nombre"}, {{"All", each
[ a = Table.Group(Table.Sort(_, {{"Fecha", Order.Ascending}}), {"Fecha"}, {{"Errors", each List.Sum([Value]), Int64.Type}}), //Sort + inner group
b = fnShift(a, "Errors", 1, null, Int64.Type), //shift rows - add prev. value
c = Table.FromColumns(Table.ToColumns(#table(null, List.Repeat({{[Nombre]{0}?}}, 2))) & Table.ToColumns(b), Value.Type(#table(type table[Nombre=text], {}) & b))
][c], type table}}),
CombinedAll = Table.Combine(GroupedRows[All]),
FilteredRows = Table.SelectRows(CombinedAll, each ([Errors_PrevValue] <> null)),
Ad_VsPrevMonth = Table.AddColumn(FilteredRows, "VS prev. Month", each Percentage.From([Errors] / [Errors_PrevValue]), Percentage.Type),
FilteredRows2 = Table.SelectRows(Ad_VsPrevMonth, each [VS prev. Month] >= 1.1)
in
FilteredRows2
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @alejandroezp, could you provide sample data and expected result based on sample data please?

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
09-18-2024 02:53 AM | |||
09-10-2024 02:30 AM | |||
09-03-2024 09:04 AM | |||
01-21-2025 06:19 PM | |||
01-11-2025 10:00 AM |
User | Count |
---|---|
28 | |
27 | |
25 | |
14 | |
10 |
User | Count |
---|---|
24 | |
21 | |
17 | |
16 | |
10 |