Crear columnas de jerarquía a partir de una columna

Uncategorized
Un usuario Pregunto ✅

kormosb

Hola

Me gustaría crear una jerarquía a partir de la tabla siguiente en Power BI para poder usarla en un objeto visual de matriz:

Jerarquía 2017 2018
1. Cuenta corriente, neto (1.1. + 1.2. + 1.3.) 2 937 -21
1.1. Bienes y servicios, netos 9 153 6000
1.1.1. Mercancías, 1 909 -1 688
1.1.1.A Exportación 85 555 88 659
1.1.1.B Importación 83 646 90 346
1.1.2. Servicios, netos 7 244 7 687
1.1.2.A Exportación 23 862 25 359
1.1.2.B Importación 16 618 17 671
1.2. Ingresos primarios, netos -5 066 -5 195
1.2.1. Compensación de los empleados, 2 624 2 322
1.2.2. Ingresos de inversión, netos -8 911 -8 716
1.2.2.1. Ingresos directos de inversión, netos -7 392 -7 402
1.2.2.2. Ingresos de inversión en cartera, netos -1 469 -1 315
1.2.2.3. Otros ingresos de inversión, netos -238 -192
1.2.2.4. Activos de reserva, netos 187 192
1.2.3. Otros ingresos primarios, netos 1 221 1200
1.2.3.1. de los cuales: transferencias de la UE 1 221 1200
1.3. Ingresos secundarios, netos -1 151 -826
1.3.1. de los cuales: transferencias de la UE 103 487
2. Cuenta de capital, neta 1075 2 996
2.1. de los cuales: transferencias de la UE 1 353 2 188
3. Cuenta financiera (activos netos) (3.1 + 3.2 + 3.3 + 3.4 + 3.5) 1 880 1168
3.1. Inversión directa (activos netos) -2 044 -2 781
3.1.1. En el extranjero (activos netos) 1 079 4 267
3.1.1.1. Patrimonio (activos netos) 215 4 819
3.1.1.1.1. Capital distinto de la reinversión de beneficios (activos netos) -855 4 015
3.1.1.1.2. Reinversión de los beneficios (activos netos) 1070 804
3.1.1.2. Instrumentos de deuda (activos netos) 864 -552
3.1.1.2.1. Activos 867 144
3.1.1.2.2. Pasivos 4 696
3.1.2 En Hungría (pasivos netos) 3 123 7 048
3.1.2.1. Patrimonio (pasivos netos) 8 458 6 487
3.1.2.1.1. Capital distinto de la reinversión de beneficios (pasivos netos) 2 320 845
3.1.2.1.2. Reinversión de beneficios (pasivos netos) 6 137 5 641
3.1.2.2. Instrumentos de deuda (pasivos netos) -5 335 561
3.1.2.2.1. Activos 3 927 1 078
3.1.2.2.2. Pasivos -1 408 1 639
3.2. Inversión en cartera (activos netos) 3 774 -185
3.2.k Activos 1 916 -140
3.2.t Pasivos -1 857 44
3.3. Derivados financieros (distintos de las reservas), activos netos -1 050 -933
3.3.k Activos -4 616 -4 432
3.3.t Pasivos -3 567 -3 499
3.4. Otras inversiones (activos netos) 1 172 1 469
3.4.k Activos 1 753 2 437
3.4.t Pasivos 580 969
3.5. Activos de reserva 28 3 598

Creo que tengo que dividir las jerarquías en diferentes columnas, así que tengo lo siguiente:

Primera jerarquía Segunda jerarquía Tercera jerarquía Cuarta jerarquía
1. Cuenta corriente, neta 1.1. Bienes y servicios, netos 1.1.1. Mercancías, 1.1.1.A Exportación
1. Cuenta corriente, neta 1.1. Bienes y servicios, netos 1.1.1. Mercancías, 1.1.1.B Importación
1. Cuenta corriente, neta 1.1. Bienes y servicios, netos 1.1.2. Servicios, netos 1.1.2.A Exportación
1. Cuenta corriente, neta 1.1. Bienes y servicios, netos 1.1.2. Servicios, netos 1.1.2.B Importación
… … … …

Me pregunto si puedo hacerlo en Power BI con DAX o debería hacer algo en Power Query / Excel?

Gracias por adelantado,

Benjamín

Amitchandak

@kormosb, ¿se muestra la matriz o tabla o jerarquía?

Para la matriz, consultar

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

para la Jerarquía se nombran
https://www.tutorialgateway.org/create-hierarchy-in-power-bi/
https://community.powerbi.com/t5/Desktop/How-to-create-Hierarchy/td-p/479913
https://stoneridgesoftware.com/creating-hierarchies-in-power-bi/
https://guyinacube.com/2019/08/07/how-to-create-and-use-a-power-bi-hierarchy/

kormosb

En respuesta a Amitchandak

Hola

Gracias por los consejos. Creo que necesito resolverlo con la jerarquía padre-hijo con las funciones PATH y PATHITEM, ¿puede ayudar con esto?

Enlace al archivo PBI.

Ben

En respuesta a kormosb

Hola @kormosb,

Perdón por respondedor tarde. Debe tener en cuenta que las funciones PATH () y PATHITEM () necesitan dos columnas diferentes como parámetros. En el archivo de ejemplo, solo tiene una sola columna que necesita una jerarquía, por lo que estas funciones no son adecuadas.

He creado una columna de índice para marcar cada jerarquía de 0 a 5:

Index = 
IF (
    CONTAINSSTRING ( LEFT ( 'Table'[Hierarchy], 1 ), "M" ),
    0,
    IF (
        CONTAINSSTRING ( LEFT ( 'Table'[Hierarchy], 3 ), " " ),
        1,
        IF (
            CONTAINSSTRING ( LEFT ( 'Table'[Hierarchy], 5 ), " " ),
            2,
            IF (
                CONTAINSSTRING ( LEFT ( 'Table'[Hierarchy], 7 ), " " ),
                3,
                IF ( CONTAINSSTRING ( LEFT ( 'Table'[Hierarchy], 9 ), " " ), 4, 5 )
            )
        )
    )
)

A continuación, se creó la columna de cuatro jerarquías utilizando las funciones containstring () y la izquierda con la columna de índice para buscar cadenas en la columna. Bajo esta lógica, si la fila solo tiene tres o menos jerarquías, mostrar la jerarquía anterior en la siguiente columna de jerarquía:

First hierarchy = 
IF(
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1"),
    "1. Current account,net",
    IF(
        CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"2"),
        "2. Capital account, net",
        IF(
            CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3"),
            "3. Financial account (net assets)",
            "Memo item"
        )
    )
)
Second hierarchy = 
SWITCH(
    TRUE(),
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "1. Current account, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.1"), "1.1 Goods and Services, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.2"), "1.2 Primary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.3"), "1.3 Secondary income, net",
    //end of 1

    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"2") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "2. Captital account, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"2") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"2.1"), "2.1 of which: EU transfers",
    //end of 2
    
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "3. Financial account (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.1"), "3.1 Direct investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.2"), "3.2 Portfolio investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.3"), "3.3 Financial derivatives (other than reserves) investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.4"), "3.4 Other investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.5"), "3.5 Reserve assets",
    //end of 3

    "Memo item"
)
Third hierarchy = 
SWITCH(
    TRUE(),
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "1. Current account, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.1") && [Index] = 2, "1.1 Goods and Services, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.1.1"), "1.1.1 Goods, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.1.2"), "1.1.2 Services, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.2") && [Index] = 2, "1.2 Primary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.2.1"), "1.2.1 Compensation of employees, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.2.2"), "1.2.2 Investment income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.2.3"), "1.2.3 Other primary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.3") && [Index] = 2, "1.3 Secondary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.3.1"), "1.3.1 of which:EU transfers",
    //end of 1


    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"2") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "2. Captital account, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"2.1") && [Index] = 2, "2.1 of which:EU transfers",
    //end of 2


    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "3. Financial account (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.1") && [Index] = 2, "3.1 Direct investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.1.1"), "3.1.1 Abroad (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.1.2"), "3.1.2 In Hungary (net liabilities)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.2") && [Index] = 2, "3.2 Portfolio investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.2.k"), "3.2.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.2.t"), "3.2.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.3") && [Index] = 2, "3.3 Financial derivatives (other than reserves) investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.3.k"), "3.3.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.3.t"), "3.3.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.4") && [Index] = 2, "3.4 Other investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.4.k"), "3.4.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.4.t"), "3.4.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.5") && [Index] = 2, "3.5 Reserve assets",
    //end of 3

    "Memo item"
)
Cuarta jerarquía ? 
SWITCH(
    TRUE(),
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && [Index] á 1, "1. Cuenta corriente, neta",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.1") && [Index] á 2, "1.1 Bienes y Servicios, neto",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.1.1") && [Index] á 3, "1.1.1 Goods, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.1.1.A"), "1.1.1.A Export",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.1.1.B"), "1.1.1.B Import",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.1.2") && [Index] á 3, "1.1.2 Services, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.1.2.A"), "1.1.2.A Export",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.1.2.B"), "1.1.2.B Import",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.2") && [Index] á 2, "1.2 Ingreso primario, neto",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.2.1") && [Index] á 3, "1.2.1 Compensación de empleados, neto",
    CONTAINSSTRING(LEFT('Table'[Jerarquía],5),"1.2.2") && [Index] á 3, "1.2.2 Ingresos de inversión, netos",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.2.2.1"), "1.2.2.1 Ingresos directos de inversión, netos",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.2.2.2"), "1.2.2.2 Ingresos de inversión en cartera, netos",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.2.2.3"), "1.2.2.3 Otros ingresos de inversión, netos",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.2.2.4"), "1.2.2.4 Activos de reserva, neto",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.2.3") && [Index] á 3, "1.2.3 Otros ingresos primarios, netos",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.2.3.1"), "1.2.3.1. de los cuales: transferencias de la UE",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.3") && [Index] á 2, "1.3 Ingresos secundarios, netos",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.3.1"), "1.3.1 de los cuales:TRANSFERENCIAs de la UE",
    finales de 1

CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"2") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "2. Cuenta captital, neta",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"2.1") && [Index] á 2, "2.1 de los cuales:Transferencias de la UE",
    finales de 2

CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && [Index] á 1, "3. Cuenta financiera (activos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.1") && [Index] á 2, "3.1 Inversión directa (activos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.1.1") && [Index] á 3, "3.1.1 En el extranjero (activos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.1.2") && [Index] á 3, "3.1.2 En Hungría (pasivos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"3.1.1.1"), "3.1.1.1. Patrimonio (activos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"3.1.1.2"), "3.1.1.2. Instrumentos de deuda (activos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"3.1.2.1"), "3.1.2.1. Patrimonio (pasivos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"3.1.2.2"), "3.1.2.2. Instrumentos de deuda (pasivos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.2") && [Index] á 2, "3.2 Inversión en cartera (activos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.2.k"), "3.2.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.2.t"), "3.2.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.3") && [Index] á 2, "3.3 Inversión en derivados financieros (excepto reservas) (activos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.3.k"), "3.3.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.3.t"), "3.3.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.4") && [Index] á 2, "3.4 Otras inversiones (activos netos)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.4.k"), "3.4.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.4.t"), "3.4.t Liabilities",CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.5") && [Index] á 2, "3.5 Activos de reserva",
    finales de 3

"Memo item"
)

El resultado final será así:

columnas de jerarquía.png

Adjunto mi relleno de muestra que espera ayudar: crear columnas de jerarquía a partir de una columna.pbix

Mejores looks,
Yingjie Li

Si este post ayuda, por favor considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

kormosb

En respuesta a v-yingjl

Hola

gracias por la solución. Generalmente funciona bien, aunque cuando quiero ponerlos en una matriz con los números no se agrega correctamente, incluso si elimino la agregación de los subtotales. (Básicamente es una declaración p’n’l)

kormosb

En respuesta a Amitchandak

Gracias por los enlaces, aunque me temo que no puedo usarlos.

Necesito crear nuevas columnas a partir de una columna como mostré en mi mensaje.

Entradas relacionadas

Problema del segundo monitor

Uncategorized

Un usuario Pregunto ✅ 6 Votar Presentado por gapage sobre 05-06-2021 02:47 p. M. Trabajo usando mi computadora portátil y un monitor separado usando «Extender…

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *