Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
alejandroezp
Frequent Visitor

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

1 ACCEPTED SOLUTION

Hi again,

 

Result

dufoq3_0-1712136072507.png

 

You can delete these steps:

dufoq3_1-1712136163298.png

 

but in CombinedTables step, you should specify your tables.

dufoq3_2-1712136309175.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

3 REPLIES 3
alejandroezp
Frequent Visitor

Buenos días,

 

Añado algunos datos de muestra:

Tabla de Febrero

NombrePinchazosMotorChapaFecha
Juan21201/02/2024
Alberto10101/02/2024
Jose01101/02/2024

 

Tabla de Marzo

NombrePinchazosMotorChapaFecha
Juan10201/03/2024
Alberto11401/03/2024
Jose10101/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

Hi again,

 

Result

dufoq3_0-1712136072507.png

 

You can delete these steps:

dufoq3_1-1712136163298.png

 

but in CombinedTables step, you should specify your tables.

dufoq3_2-1712136309175.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

dufoq3
Super User
Super User

Hi @alejandroezp, could you provide sample data and expected result based on sample data please?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors