Solved: Nivel de fila Cálculo porcentual para cada línea q...

harirao · ‎05-22-2020

Hola a todos

Quiero calcular la diferencia %, dando 9 condición mencionada a continuación para cada cálculo de nivel de fila.

S.No	CURP	Urp	SFRP	Diferencia %	Dax para cada cálculo de nivel de fila
1				N/A	SI las tres columnas están en blanco
2	100		90	(90/100)%	SFRP/CURP. Cuando URP está en blanco
3		50	90	(90/50)%	SFRP/URP. Cuando CURP está en blanco
4			90	(90/1)%	SFRP/1. Cuando CURP y URP están en blanco
5	100			(1/100)%	1/CURP. Cuando SFRP y URP están en blanco
6		50		(1/50)%	1/URP. Cuando SFRP y CURP están en blanco
7	100	100	100	(100/100)% - 0%	Debe ser 0% cuando todos tienen el mismo valor
8	0	100	50	50%	Cuando CURP es 0 considere el valor SFRP como Porcentaje
9	0	0	50	50%	Cuando CURP y URP son 0, considere el valor SFRP como Porcentaje
10	0	0	0	0%	Cuando CURP, URP y SFRP son 0, considere el valor como Porcentaje

Por favor, compruebe y corríjame si por debajo de DAX es correcto para la Condición 2 a 6 que intenté.
Diffirent% =

Condición-2

IF('DATA'[URP-Unconstrained Requirement Plan]-BLANK(),

DIVIDE('DATA'[SFRP- Submitted Final Requirement Plan],'DATA'[CURP-Consolidated URP]),

Condición-3

IF('DATA'[CURP-Consolidated URP]-BLANK(),

DIVIDE('DATA'[SFRP- Submitted Final Requirement Plan],'DATA'[URP-Unconstrained Requirement Plan]),

Condición-4

IF(AND('DATA'[CURP-Consolidated URP]-BLANK(),'DATA'[URP-Unconstrained Requirement Plan]-BLANK()),

DIVIDE('DATA'[SFRP- Plan de Requisito Final Presentado],1),

Condición-5

IF(AND('DATA'[URP-Unconstrained Requirement Plan]-BLANK(),'DATA'[SFRP- Submitted Final Requirement Plan]-BLANK()),

DIVIDE(1,'DATA'[CURP-Consolidated URP]),

condición-6

IF(AND('DATA'[CURP-Consolidated URP]-BLANK(),'DATA'[SFRP- Submitted Final Requirement Plan]-BLANK()),

DIVIDE(1,'DATA'[URP-Unconstrained Requirement Plan],0))))))
No pude dar condition1, donde las tres filas están en blanco es NA, aquí las tres columnas están en formato "Número entero"
Por favor, ayúdame en las condiciones restantes también en DAX único.

PBI trabajando

saludos
Hari

v-lid-msft · ‎05-27-2020

Hola @harirao ,

Para la medida, utilice lo siguiente:

Medida de Porcentajedesa
INTERRUPTOR (
    VERDADERO (),
    Condición No.1
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && SUM ( 'Datos'[CURP-Consolidated URP] ) > 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0, DIVIDE (
        SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ),
        SUM ( 'Datos'[URP consolidado CURP] )
    ),
    Condición No.2
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
    ) á 0
        && SUM ( 'Data'[URP-Unconstrained Requirement Plan] ) > 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0, DIVIDE (
        SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ),
        SUM ( 'Datos'[Plan de requisitos sin restricciones URP] )
    ),
    Condición No.3
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
    ) á 0
        && SUM ( 'Datos'[URP consolidado CURP] ) > 0, DIVIDE ( 1, SUM ( 'Data'[CURP-Consolidated URP] ) ),
    Condición No.4
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
        ) á 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0, DIVIDE ( SUM ( 'Data' [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.19
    SUM ( 'Datos'[URP consolidado CURP] ) - 0
        && SUM ( 'Datos'[URP-Plan de requisitos sin restricciones] )
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0, DIVIDE ( SUM ( 'Data' [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.6
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
        ) á 0
        && SUM ( 'Datos'[URP-Plan de requisitos sin restricciones] ) > 0, DIVIDE ( 1, SUM ( 'Data'[URP-Unconstrained Requirement Plan] ) ),
    Condición No.7
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
        ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
        ) - 0, 0,
    Condición No.8 y Condición No.9 
    SUM ( 'Datos'[Plan de requisitos sin restricciones URP] )
        • SUM ('Datos'[SFRP- Plan de requisitos finales presentado] )
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] )
            • SUM ( 'Datos'[URP consolidado CURP] ), 0,
    Condición No.10 y Condición No.12
    SUM ( [CURP-Consolidated URP] ) - 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( 'Datos'[URP-Plan de requisitos sin restricciones] ) > 0, DIVIDE ( SUM ( 'Data' [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.11
    SUM ( 'Datos'[URP-Plan de requisitos sin restricciones] ) - 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( 'Datos'[URP consolidado CURP] ) > 0, DIVIDE (
        SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ),
        SUM ( 'Datos'[URP consolidado CURP] )
    ),
    Condición No.13
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( 'Datos'[URP consolidado CURP] ) - 0, DIVIDE ( SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ), 100 ),
    Condición No.14
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
    ) á 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( 'Datos'[URP-Plan de requisitos sin restricciones] ) - 0, DIVIDE ( SUM ( 'Data'[SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.15 y Condición No.16 y Condición No.17
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
    ) á 0
        && (
            SUM ( 'Datos'[URP consolidado CURP] ) - 0
                || CALCULAR (
                    COUNTROWS ( 'Datos' ),
                    NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
                ) á 0
        ), 0,
    Condición No.18
    SUM ( 'Datos'[SFRP- Presentado FinalPlan de requisitos] ) , 0, 0,
    Condición predeterminada
    DIVIDE (
        SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ),
        SUM ( 'Datos'[URP consolidado CURP] ),
        0 )
)

Para el mismo número, creemos que puede porque es un número decimel pero mostrar como número entero.

Para coulmn:

Diffirent% Column =
SWITCH (
    TRUE (),
    //Condition No.1
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [CURP-Consolidated URP] > 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ),
    //Condition No.2
    ISBLANK ( [CURP-Consolidated URP] )
        && [URP-Unconstrained Requirement Plan] > 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE (
        [SFRP- Submitted Final Requirement Plan],
        [URP-Unconstrained Requirement Plan]
    ),
    //Condition No.3
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, [CURP-Consolidated URP] ),
    //Condition No.4
    ISBLANK ( [CURP-Consolidated URP] )
        && ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.19
    [CURP-Consolidated URP] = 0
        && [URP-Unconstrained Requirement Plan] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.6
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] )
        && [URP-Unconstrained Requirement Plan] > 0, DIVIDE ( 1, [URP-Unconstrained Requirement Plan] ),
    //Condition No.7
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] ), 0,
    //Condition No.8 & Condition No.9 
    ROUND (
        [URP-Unconstrained Requirement Plan],
        0
    )
        = ROUND ( [SFRP- Submitted Final Requirement Plan], 0 )
        && ROUND ( [SFRP- Submitted Final Requirement Plan], 0 )
            = ROUND ( [CURP-Consolidated URP], 0 ), 0,
    //Condition No.10 & Condition No.12
    [CURP-Consolidated URP] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [URP-Unconstrained Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.11
    [URP-Unconstrained Requirement Plan] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [CURP-Consolidated URP] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ),
    //Condition No.13
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [CURP-Consolidated URP] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.14
    ISBLANK ( [CURP-Consolidated URP] )
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [URP-Unconstrained Requirement Plan] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.15 & Condition No.16 & Condition No.17
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && (
            [CURP-Consolidated URP] = 0
                || ISBLANK ( [CURP-Consolidated URP] )
        ), 0,
    //Condition No.18
    [SFRP- Submitted Final Requirement Plan] = 0, 0,
    //Default Condition
    DIVIDE (
        [SFRP- Submitted Final Requirement Plan],
        [CURP-Consolidated URP],
        0
    )
)

Para la medida:

Medida de Porcentajedesa
INTERRUPTOR (
    VERDADERO (),
    Condición No.1
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && SUM ( 'Datos'[CURP-Consolidated URP] ) > 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0, DIVIDE (
        SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ),
        SUM ( 'Datos'[URP consolidado CURP] )
    ),
    Condición No.2
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
    ) á 0
        && SUM ( 'Data'[URP-Unconstrained Requirement Plan] ) > 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0, DIVIDE (
        SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ),
        SUM ( 'Datos'[Plan de requisitos sin restricciones URP] )
    ),
    Condición No.3
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
    ) á 0
        && SUM ( 'Datos'[URP consolidado CURP] ) > 0, DIVIDE ( 1, SUM ( 'Data'[CURP-Consolidated URP] ) ),
    Condición No.4
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
        ) á 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0, DIVIDE ( SUM ( 'Data' [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.19
    SUM ( 'Datos'[URP consolidado CURP] ) - 0
        && SUM ( 'Datos'[URP-Plan de requisitos sin restricciones] )
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0, DIVIDE ( SUM ( 'Data' [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.6
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
        ) á 0
        && SUM ( 'Datos'[URP-Plan de requisitos sin restricciones] ) > 0, DIVIDE ( 1, SUM ( 'Data'[URP-Unconstrained Requirement Plan] ) ),
    Condición No.7
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
        ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
        ) - 0, 0,
    Condición No.8 y Condición No.9 
    ROUND (
        SUM ( 'Datos'[Plan de requisitos sin restricciones URP] ),
        0
    )
        • ROUND ( SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ), 0 )
        && ROUND ( SUM ( 'Data'[SFRP- Submitted Final Requirement Plan] ), 0 )
            • ROUND ( SUM ( 'Datos'[URP consolidado CURP] ), 0 ), 0,
    Condición No.10 y Condición No.12
    SUM ( [CURP-Consolidated URP] ) - 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( 'Datos'[URP-Plan de requisitos sin restricciones] ) > 0, DIVIDE ( SUM ( 'Data' [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.11
    SUM ( 'Datos'[URP-Plan de requisitos sin restricciones] ) - 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( 'Datos'[URP consolidado CURP] ) > 0, DIVIDE (
        SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ),
        SUM ( 'Datos'[URP consolidado CURP] )
    ),
    Condición No.13
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( 'Datos'[URP consolidado CURP] ) - 0, DIVIDE ( SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ), 100 ),
    Condición No.14
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
    ) á 0
        && SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( 'Datos'[URP-Plan de requisitos sin restricciones] ) - 0, DIVIDE ( SUM ( 'Data'[SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.15 y Condición No.16 y Condición No.17
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
    ) á 0
        && (
            SUM ( 'Datos'[URP consolidado CURP] ) - 0
                || CALCULAR (
                    COUNTROWS ( 'Datos' ),
                    NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
                ) á 0), 0,
    Condición No.18
    SUM ( 'Datos'[SFRP- Plan de requisitos finales presentados] ) - 0, 0,
    Condición predeterminada
    DIVIDE (
        SUM ( 'Datos'[SFRP- Plan de requisitos finales presentado] ),
        SUM ( 'Datos'[URP consolidado CURP] ),
        0 )
)

Saludos

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

View solution in original post

amitchandak · ‎05-22-2020

@harirao, no se ha consigo por completo. Puede utilizar switch true

https://community.powerbi.com/t5/Desktop/Help-with-SWITCH-TRUE-with-multiple-items-to-evaluate/td-p/...

!! Power BI 101 Interview questions !! !! Master Microsoft Fabric- 36 Videos !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

harirao · ‎05-22-2020

Hola amitchandak,
Quiero calcular un Porcentaje para la siguiente tabla en el nivel de fila, si tengo que dar 10 condición

Condición a aplicar

¿Espero no haber confundido ahora?

saludos

Día

harirao · ‎05-22-2020

Hola a todos

¿Puede alguien darme una solución para esto?

saludos

Día

v-lid-msft · ‎05-22-2020

Hola @harirao ,

Podemos intentar crear una medida para satisfacer sus requisitos:

Diffirent% =
SWITCH (
    TRUE (),
    //condition No. 1
    CALCULATE (
        COUNTROWS ( 'DATA' ),
        ISBLANK ( 'DATA'[URP-Unconstrained Requirement Plan] ),
        ISBLANK ( 'DATA'[SFRP- Submitted Final Requirement Plan] ),
        ISBLANK ( 'DATA'[CURP-Consolidated URP] )
    )
        = COUNTROWS ( 'DATA' ), BLANK (),
//condition No. 4
    CALCULATE (
        COUNTROWS ( 'DATA' ),
        ISBLANK ( 'DATA'[CURP-Consolidated URP] ),
        ISBLANK ( 'DATA'[URP-Unconstrained Requirement Plan] )
    )
        = COUNTROWS ( 'DATA' ), DIVIDE ( SUM ( 'DATA'[SFRP- Submitted Final Requirement Plan] ), 1 ) / 100,
    //condition No. 5
    CALCULATE (
        COUNTROWS ( 'DATA' ),
        ISBLANK ( 'DATA'[SFRP- Submitted Final Requirement Plan] ),
        ISBLANK ( 'DATA'[URP-Unconstrained Requirement Plan] )
    )
        = COUNTROWS ( 'DATA' ), DIVIDE ( 1, SUM ( 'DATA'[CURP-Consolidated URP] ) ) / 100,
    //condition No. 6
    CALCULATE (
        COUNTROWS ( 'DATA' ),
        ISBLANK ( 'DATA'[SFRP- Submitted Final Requirement Plan] ),
        ISBLANK ( 'DATA'[CURP-Consolidated URP] )
    )
        = COUNTROWS ( 'DATA' ), DIVIDE ( 1, SUM ( 'DATA'[URP-Unconstrained Requirement Plan] ) ) / 100,
    //condition No. 2
    CALCULATE (
        COUNTROWS ( 'DATA' ),
        ISBLANK ( 'DATA'[URP-Unconstrained Requirement Plan] )
    )
        = COUNTROWS ( 'DATA' ), DIVIDE (
        SUM ( 'DATA'[SFRP- Submitted Final Requirement Plan] ),
        SUM ( 'DATA'[CURP-Consolidated URP] )
    ) / 100,
    //condition No. 3
    CALCULATE (
        COUNTROWS ( 'DATA' ),
        ISBLANK ( 'DATA'[CURP-Consolidated URP] )
    )
        = COUNTROWS ( 'DATA' ), DIVIDE (
        SUM ( 'DATA'[SFRP- Submitted Final Requirement Plan] ),
        SUM ( 'DATA'[URP-Unconstrained Requirement Plan] )
    ) / 100,
    
    //condition No. 7
    SUM ( 'DATA'[URP-Unconstrained Requirement Plan] )
        = SUM ( 'DATA'[CURP-Consolidated URP] )
        && SUM ( 'DATA'[CURP-Consolidated URP] )
            = SUM ( 'DATA'[SFRP- Submitted Final Requirement Plan] ), 0,
    //condition No. 8
    SUM ( 'DATA'[CURP-Consolidated URP] ) = 0, DIVIDE ( SUM ( 'DATA'[SFRP- Submitted Final Requirement Plan] ), 100 ),
    //condition No. 9
    SUM ( 'DATA'[CURP-Consolidated URP] ) = 0
        && SUM ( 'DATA'[URP-Unconstrained Requirement Plan] ) = 0, DIVIDE ( SUM ( 'DATA'[SFRP- Submitted Final Requirement Plan] ), 100 ),
    //condition No. 10
    SUM ( 'DATA'[CURP-Consolidated URP] ) = 0
        && SUM ( 'DATA'[URP-Unconstrained Requirement Plan] ) = 0
        && SUM ( 'DATA'[SFRP- Submitted Final Requirement Plan] ) = 0, 0
)

Si perfer una columna calculada, intente utilizar lo siguiente,

Diffirent% Column =
SWITCH (
    TRUE (),
    //condition No. 1
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] ), BLANK (),
    //condition No. 4
    ISBLANK ( [CURP-Consolidated URP] )
        && ISBLANK ( [URP-Unconstrained Requirement Plan] ), DIVIDE ( [SFRP- Submitted Final Requirement Plan], 1 ) / 100,
    //condition No. 5
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [URP-Unconstrained Requirement Plan] ), DIVIDE ( 1, [CURP-Consolidated URP] ) / 100,
    //condition No. 6
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] ), DIVIDE ( 1, [URP-Unconstrained Requirement Plan] ) / 100,
    //condition No. 2
    ISBLANK ( [URP-Unconstrained Requirement Plan] ), DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ) / 100,
    //condition No. 3
    ISBLANK ( [CURP-Consolidated URP] ), DIVIDE (
        [SFRP- Submitted Final Requirement Plan],
        [URP-Unconstrained Requirement Plan]
    ) / 100,
    //condition No. 7
    [URP-Unconstrained Requirement Plan] = [CURP-Consolidated URP]
        && [CURP-Consolidated URP] = [SFRP- Submitted Final Requirement Plan], 0,
    //condition No. 8
    [CURP-Consolidated URP] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //condition No. 9
    [CURP-Consolidated URP] = 0
        && [URP-Unconstrained Requirement Plan] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //condition No. 10
    [CURP-Consolidated URP] = 0
        && [URP-Unconstrained Requirement Plan] = 0
        && [SFRP- Submitted Final Requirement Plan] = 0, 0
)

Por favor, tenga en cuenta que si hay varias filas para cada ID de producto y Ubicación, el resultado de la medida y la columna podría ser diferente.

tales como seguir

Id	Ubicación	CUPR	Upr	SFPR	%Columna de diferencia
1	A			50	50%
1	A	50			2%
1	A		50		2%

Pero cuando está en la tabla visual:

Id	Ubicación	CUPR	Upr	SFPR	%Medida de diferencia
1	A	50	50	50	0%

Si no cumple con su requisito, ¿podría mostrar el resultado exacto esperado basado en las tablas que ha compartido?

Saludos

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

harirao · ‎05-22-2020

Hola v-member-msft,

Se requieren algunas entradas más en Diferente % de columna
1. El cálculo por defecto % es SFRP/CURP cuando URP está en blanco o 0 por ejemplo: (1109/710-156%), (48/3-16%) & & (277/103 x 269%) pero obteniendo un resultado erróneo.
2. El resultado no se muestra (92/46o 2%) SFRP/CURP cuando URP está en blanco o 0
3. Cuando CURP está en blanco y URP es 0 mismo SFRP debe ser resultado debe, es decir, 52% y 152% mencionado en la siguiente instantánea.

saludos

Día

harirao · ‎05-22-2020

Hola v-lid-msft,Una cosa más quería saber con respecto a la Condición-5
Cuando SFRP está en blanco, considere curP que es 1/CURP

¿Puedes por favor ayudarme en esto?

saludos

Día

v-lid-msft · ‎05-22-2020

Hola @harirao ,

Por favor, intente cambiar la condición 5 como la siguiente:

Diffirent% Column =
SWITCH (
    TRUE (),
    //condition No. 1
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] ), BLANK (),
    //condition No. 4
    ISBLANK ( [CURP-Consolidated URP] )
        && (
            ISBLANK ( [URP-Unconstrained Requirement Plan] )
                || [URP-Unconstrained Requirement Plan] = 0
        ), DIVIDE ( [SFRP- Submitted Final Requirement Plan], 1 ) / 100,
    //condition No. 5
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && NOT ( ISBLANK ( [CURP-Consolidated URP] ) ), DIVIDE ( 1, [CURP-Consolidated URP] ) / 100,
    //condition No. 6
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] ), DIVIDE ( 1, [URP-Unconstrained Requirement Plan] ) / 100,
    //condition No. 2
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        || [URP-Unconstrained Requirement Plan] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ) / 100,
    //condition No. 3
    ISBLANK ( [CURP-Consolidated URP] ), DIVIDE (
        [SFRP- Submitted Final Requirement Plan],
        [URP-Unconstrained Requirement Plan]
    ) / 100,
    //condition No. 7
    [URP-Unconstrained Requirement Plan] = [CURP-Consolidated URP]
        && [CURP-Consolidated URP] = [SFRP- Submitted Final Requirement Plan], 0,
    //condition No. 8
    [CURP-Consolidated URP] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //condition No. 9
    [CURP-Consolidated URP] = 0
        && [URP-Unconstrained Requirement Plan] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //condition No. 10
    [CURP-Consolidated URP] = 0
        && [URP-Unconstrained Requirement Plan] = 0
        && [SFRP- Submitted Final Requirement Plan] = 0, 0
)

Saludos

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

harirao · ‎05-22-2020

Hola v-lid-msft,

Se han realizado los cambios a continuación, el resultado no es preciso
Eg1: (214/161 - 133%) condición3: SFRP/URP. Cuando CURP está en blanco.Por ejemplo2:Las columnas de porcentaje no deben estar en blanco según la condición 9- Cuando CURP y URP son 0, considere el valor SFRP como porcentaje.Uno más me di cuenta de que un montón de filas de Blanks aprehendo, por favor ayúdame en esto también

saludos

Día

v-lid-msft · ‎05-22-2020

Hola @harirao ,

Movemos la condición 9 a la segunda de la siguiente manera:

Diffirent% Column =
SWITCH (
    TRUE (),
    //condition No. 1
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] ), BLANK (),
    //condition No. 9
    [CURP-Consolidated URP] = 0
        && [URP-Unconstrained Requirement Plan] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //condition No. 4
    ISBLANK ( [CURP-Consolidated URP] )
        && (
            ISBLANK ( [URP-Unconstrained Requirement Plan] )
                || [URP-Unconstrained Requirement Plan] = 0
        ), DIVIDE ( [SFRP- Submitted Final Requirement Plan], 1 ) / 100,
    //condition No. 5
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && NOT ( ISBLANK ( [CURP-Consolidated URP] ) ), DIVIDE ( 1, [CURP-Consolidated URP] ) / 100,
    //condition No. 6
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] ), DIVIDE ( 1, [URP-Unconstrained Requirement Plan] ) / 100,
    //condition No. 2
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        || [URP-Unconstrained Requirement Plan] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ) / 100,
    //condition No. 3
    ISBLANK ( [CURP-Consolidated URP] ), DIVIDE (
        [SFRP- Submitted Final Requirement Plan],
        [URP-Unconstrained Requirement Plan]
    ) / 100,
    //condition No. 7
    [URP-Unconstrained Requirement Plan] = [CURP-Consolidated URP]
        && [CURP-Consolidated URP] = [SFRP- Submitted Final Requirement Plan], 0,
    //condition No. 8
    [CURP-Consolidated URP] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //condition No. 10
    [CURP-Consolidated URP] = 0
        && [URP-Unconstrained Requirement Plan] = 0
        && [SFRP- Submitted Final Requirement Plan] = 0, 0
)

¿Podría ampliar el formulario de condiciones en la respuesta 4 para que podamos modificar la fórmula? Por favor considere la condición en blanco y cero también.

Saludos

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

harirao · ‎05-22-2020

Hola v-member-msft,

¿Puede sordme en este cálculo de DAX?

saludos

Día

v-lid-msft · ‎05-22-2020

Hola @harirao ,

Por favor, compruebe el siguiente dax basado en el excel compartido:

Diffirent% Column =
SWITCH (
    TRUE (),
    //Condition No.1
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [CURP-Consolidated URP] > 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ),
    //Condition No.2
    ISBLANK ( [CURP-Consolidated URP] )
        && [URP-Unconstrained Requirement Plan] > 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE (
        [SFRP- Submitted Final Requirement Plan],
        [URP-Unconstrained Requirement Plan]
    ),
    //Condition No.3
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, [CURP-Consolidated URP] ),
    //Condition No.4
    ISBLANK ( [CURP-Consolidated URP] )
        && ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.5
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, [CURP-Consolidated URP] ),
    //Condition No.6
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] )
        && [URP-Unconstrained Requirement Plan] > 0, DIVIDE ( 1, [URP-Unconstrained Requirement Plan] ),
    //Condition No.7
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] ), BLANK (),
    //Condition No.8 & Condition No.9 
    [URP-Unconstrained Requirement Plan] = [SFRP- Submitted Final Requirement Plan]
        && [SFRP- Submitted Final Requirement Plan] = [CURP-Consolidated URP], 0,
    //Condition No.10 & Condition No.12
    [CURP-Consolidated URP] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [URP-Unconstrained Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.11
    [URP-Unconstrained Requirement Plan] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [CURP-Consolidated URP] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ),
    //Condition No.13
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [CURP-Consolidated URP] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.14
    ISBLANK ( [CURP-Consolidated URP] )
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [URP-Unconstrained Requirement Plan] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.15 & Condition No.16 & Condition No.17
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && (
            [CURP-Consolidated URP] = 0
                || ISBLANK ( [CURP-Consolidated URP] )
        ), BLANK (),
    //Condition No.18
    [SFRP- Submitted Final Requirement Plan] = 0, 0
)

Puede sáquelo fácilmente si tiene más afección:

Diffirent% Column =
SWITCH (
    TRUE (),
    //Condition No.1
    [Condition Logic 1], [Calculate Formula 1],
    //Condition No.2
    [Condition Logic 2], [Calculate Formula 2],
    //Condition No.3
    [Condition Logic 3], [Calculate Formula 3],
    [Other Calculate Formula]
)

La condición anterior se coincidirá primero y se calculará, si coincide, la siguiente condición no intentará coincidir.

Saludos

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

harirao · ‎05-22-2020

Hola v-lid-msft,

S.No	CURP	Urp	SFRP	Condición	Resultado
2		50	90	SFRP/URP. Cuando CURP está en blanco	(90/50)*100 á 180%

El cálculo a continuación muestra mal.

Los espacios en blanco deben reemplazarse por el valor SFRP como porcentaje.

CURP	Urp	SFRP	Condición	Resultado
0	0	90	Cuando CURP y SFRP son 0 considere el valor SFRP como Porcentaje	90%

saludos

Día

harirao · ‎05-22-2020

Holav-lid-msft,

Por favor, ayúdame en el siguiente cálculo.

saludos

Día

harirao · ‎05-22-2020

Hola @v-lid-msft,

Se requiere su ayuda para calcular este DAX.

Gracias y saludos,
Hari

v-lid-msft · ‎05-22-2020

Hola @harirao ,

Para la segunda captura de pantalla, podemos agregar una condición 19 para resolverlo:

Diffirent% Column =
SWITCH (
    TRUE (),
    //Condition No.1
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [CURP-Consolidated URP] > 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ),
    //Condition No.2
    ISBLANK ( [CURP-Consolidated URP] )
        && [URP-Unconstrained Requirement Plan] > 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE (
        [SFRP- Submitted Final Requirement Plan],
        [URP-Unconstrained Requirement Plan]
    ),
    //Condition No.3
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, [CURP-Consolidated URP] ),
    //Condition No.4
    ISBLANK ( [CURP-Consolidated URP] )
        && ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.19
    [CURP-Consolidated URP] = 0
        && [URP-Unconstrained Requirement Plan] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.5
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, [CURP-Consolidated URP] ),
    //Condition No.6
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] )
        && [URP-Unconstrained Requirement Plan] > 0, DIVIDE ( 1, [URP-Unconstrained Requirement Plan] ),
    //Condition No.7
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] ), BLANK (),
    //Condition No.8 & Condition No.9 
    [URP-Unconstrained Requirement Plan] = [SFRP- Submitted Final Requirement Plan]
        && [SFRP- Submitted Final Requirement Plan] = [CURP-Consolidated URP], 0,
    //Condition No.10 & Condition No.12
    [CURP-Consolidated URP] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [URP-Unconstrained Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.11
    [URP-Unconstrained Requirement Plan] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [CURP-Consolidated URP] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ),
    //Condition No.13
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [CURP-Consolidated URP] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.14
    ISBLANK ( [CURP-Consolidated URP] )
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [URP-Unconstrained Requirement Plan] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.15 & Condition No.16 & Condition No.17
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && (
            [CURP-Consolidated URP] = 0
                || ISBLANK ( [CURP-Consolidated URP] )
        ), BLANK (),
    //Condition No.18
    [SFRP- Submitted Final Requirement Plan] = 0, 0
)

Pero la fila 1272% parece estar combinada por multi %Column, ¿podría sortearla en bruto o usar la siguiente medida para verificarla?

Verify = Count(Diffirent% Column)

También tenga en cuenta que debido a que no podemos generar N/A junto con el formato de número, por lo que en alguna condición dará salida en blanco, como Condición 7 y Condición 15-17

Saludos

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

harirao · ‎05-22-2020

Hola @v-lid-msft,

Gracias por su respuesta, Sí para 1272% parece ser combinado por multi %Column.He utilizado el mismo dax en mi archivo pbix principal, todavía condición principal/condición predeterminada SFRP/CURP no está funcionando.
Por ejemplo: (15/5-300%) (20/42-48%) (244/267-91%) &(668/549-122%) etc..

Diferencia% =

INTERRUPTOR (

VERDADERO (),

Condición No.1

ISBLANK ( [Plan de requisitos sin restricciones (URP)] )

&& [URP consolidado] > 0

&& [Plan de requisitos finales presentado] > 0, DIVIDE ( [Plan de requisitos finales presentado], [URP consolidado] ),

Condición No.2

ISBLANK ( [URP consolidado] )

&& [Plan de requisitos sin restricciones (URP)] > 0

&& [Plan de requisitos finales presentado] > 0, DIVIDE (

[Plan de Requisitos Finales Presentado],

[Plan de requisitos sin restricciones (URP)]

),

Condición No.3

ISBLANK ( [Plan de requisitos sin restricciones (URP)] )

&& ISBLANK ( [Plan de requisitos finales presentado] )

&& [URP consolidado] > 0, DIVIDE ( 1, [URP consolidado] ),

Condición No.4

ISBLANK ( [URP consolidado] )

&& ISBLANK ( [Plan de requisitos sin restricciones (URP)] )

&& [Plan de requisitos finales presentado] > 0, DIVIDE ( [Plan de requisitos finales presentado], 100 ),

Condición No.19

[URP consolidado] - 0

&& [Plan de requisitos sin restricciones (URP)]

&& [Plan de requisitos finales presentado] > 0, DIVIDE ( [Plan de requisitos finales presentado], 100 ),

Condición No.5

ISBLANK ( [Plan de requisitos sin restricciones (URP)] )

&& ISBLANK ( [Plan de requisitos finales presentado] )

&& [URP consolidado] > 0, DIVIDE ( 1, [URP consolidado] ),

Condición No.6

ISBLANK ( [Plan de requisitos finales presentado] )

&& ISBLANK ( [URP consolidado] )

&& [Plan de requisitos sin restricciones (URP)] > 0, DIVIDE ( 1, [Unconstrained Requirement Plan (URP)] ),

Condición No.7

ISBLANK ( [Plan de requisitos sin restricciones (URP)] )

&& ISBLANK ( [Plan de requisitos finales presentado] )

&& ISBLANK ( [URP consolidado] ), BLANK (),

Condición No.8 y Condición No.9

[Plan de requisitos sin restricciones (URP)] [ Plan de requisitos finales enviado]

&& [Plan de requisitos finales presentado] - [URP consolidado], 0,

Condición No.10 y Condición No.12

[URP consolidado] - 0

&& [Plan de requisitos finales presentado] > 0

&& [Plan de requisitos sin restricciones (URP)] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),

Condición No.11

[Plan de requisitos sin restricciones (URP)]

&& [Plan de requisitos finales presentado] > 0

&& [URP consolidado] > 0, DIVIDE ( [Plan de requisitos finales presentado], [URP consolidado] ),

Condición No.13

ISBLANK ( [Plan de requisitos sin restricciones (URP)] )

&& [Plan de requisitos finales presentado] > 0

&& [URP consolidado] - 0, DIVIDE ( [Plan de requisitos finales presentado], 100 ),

Condición No.14

ISBLANK ( [URP consolidado] )

&& [Plan de requisitos finales presentado] > 0

&& [Plan de requisitos sin restricciones (URP)] a 0, DIVIDE ( [Plan de requisitos finales presentado], 100 ),

Condición No.15 y Condición No.16 y Condición No.17

ISBLANK ( [Plan de requisitos finales presentado] )

&& (

[URP consolidado] - 0

|| ISBLANK ( [URP consolidado] )

), EN BLANCO (),

Condición No.18

[Plan de Requisitofinal Presentado] - 0, 0

)

Verificar á Count(Difference%)

Si la condición anterior cumple Deafult (SFRP/CURP) remainig como "0" , ya que tenemos el formato de número

Gracias.

saludos
Hari

harirao · ‎05-22-2020

Hola @v-lid-msft
¿Puede sordme en este cálculo de DAX?

saludos

Día

v-lid-msft · ‎05-22-2020

Hola @harirao ,

Consulte el siguiente dax, el conditón predeterminado es S/C, y N/A se mostrará como 0 en lugar de en blanco

Diffirent% Column =
SWITCH (
    TRUE (),
    //Condition No.1
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [CURP-Consolidated URP] > 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ),
    //Condition No.2
    ISBLANK ( [CURP-Consolidated URP] )
        && [URP-Unconstrained Requirement Plan] > 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE (
        [SFRP- Submitted Final Requirement Plan],
        [URP-Unconstrained Requirement Plan]
    ),
    //Condition No.3
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, [CURP-Consolidated URP] ),
    //Condition No.4
    ISBLANK ( [CURP-Consolidated URP] )
        && ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.19
    [CURP-Consolidated URP] = 0
        && [URP-Unconstrained Requirement Plan] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.5
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, [CURP-Consolidated URP] ),
    //Condition No.6
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] )
        && [URP-Unconstrained Requirement Plan] > 0, DIVIDE ( 1, [URP-Unconstrained Requirement Plan] ),
    //Condition No.7
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && ISBLANK ( [CURP-Consolidated URP] ), 0,
    //Condition No.8 & Condition No.9 
    [URP-Unconstrained Requirement Plan] = [SFRP- Submitted Final Requirement Plan]
        && [SFRP- Submitted Final Requirement Plan] = [CURP-Consolidated URP], 0,
    //Condition No.10 & Condition No.12
    [CURP-Consolidated URP] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [URP-Unconstrained Requirement Plan] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.11
    [URP-Unconstrained Requirement Plan] = 0
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [CURP-Consolidated URP] > 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], [CURP-Consolidated URP] ),
    //Condition No.13
    ISBLANK ( [URP-Unconstrained Requirement Plan] )
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [CURP-Consolidated URP] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.14
    ISBLANK ( [CURP-Consolidated URP] )
        && [SFRP- Submitted Final Requirement Plan] > 0
        && [URP-Unconstrained Requirement Plan] = 0, DIVIDE ( [SFRP- Submitted Final Requirement Plan], 100 ),
    //Condition No.15 & Condition No.16 & Condition No.17
    ISBLANK ( [SFRP- Submitted Final Requirement Plan] )
        && (
            [CURP-Consolidated URP] = 0
                || ISBLANK ( [CURP-Consolidated URP] )
        ), 0,
    //Condition No.18
    [SFRP- Submitted Final Requirement Plan] = 0, 0,
    //Default Condition
    DIVIDE (
        [SFRP- Submitted Final Requirement Plan],
        [CURP-Consolidated URP],
        0
    )
)

Pero para la condición de varias filas sin procesar, creemos que el uso de la medida será mejor, consulte la respuesta 6 en este hilo para obtener más información sobre esta situación. transformaremos esta columna calculada en medida en pocos días.

Saludos

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

harirao · ‎05-22-2020

Hola @v-lid-msft,

Gracias por toda su ayuda, %Colum está funcionando correctamente.

saludos

Día

v-lid-msft · ‎05-22-2020

Hola @harirao ,

Si desea una solución de medida (en caso de que una fila en la tabla visual contenga varias filas sin procesar), consulte lo siguiente, cambie "Datos" al nombre de la tabla:

Diffirent% Columna ?
INTERRUPTOR (
    VERDADERO (),
    Condición No.1
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && [CURP-Consolidated URP] > 0
        && SUM ( [SFRP- Plan de requisitos finales presentado] ) > 0, DIVIDE (
        SUM ( [SFRP- Plan de requisitos finales presentado] ),
        SUM ( [CURP-Consolidated URP] )
    ),
    Condición No.2
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
    ) á 0
        && SUM ( [URP-Unconstrained Requirement Plan] ) > 0
        && SUM ( [SFRP- Plan de requisitos finales presentado] ) > 0, DIVIDE (
        SUM ( [SFRP- Plan de requisitos finales presentado] ),
        SUM ( [URP-Unconstrained Requirement Plan] )
    ),
    Condición No.3
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
        ) á 0
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, SUM ( [CURP-Consolidated URP] ) ),
    Condición No.4
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
        ) á 0
        && SUM ( [SFRP- Submitted Final Requirement Plan] ) > 0, DIVIDE ( SUM ( [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.19
    SUM ( [CURP-Consolidated URP] ) - 0
        && SUM ( [URP-Plan de requisitos sin restricciones] )
        && SUM ( [SFRP- Submitted Final Requirement Plan] ) > 0, DIVIDE ( SUM ( [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.5
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
        ) á 0
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, SUM ( [CURP-Consolidated URP] ) ),
    Condición No.6
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
        ) á 0
        && SUM ( [URP-Unconstrained Requirement Plan] ) > 0, DIVIDE ( 1, SUM ( [URP-Unconstrained Requirement Plan] ) ),
    Condición No.7
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
        ) á 0
        && CALCULATE (
            COUNTROWS ( 'Datos' ),
            NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
        ) - 0, 0,
    Condición No.8 y Condición No.9 
    [Plan de requisitos sin restricciones de URP] [ SFRP- Plan de requisitos finales presentado]
        && [SFRP- Plan de requisitos finales presentado] - [CURP-Consolidated URP], 0,
    Condición No.10 y Condición No.12
    SUM ( [CURP-Consolidated URP] ) - 0
        && SUM ( [SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( [URP-Plan de requisitos sin restricciones] ) > 0, DIVIDE ( SUM ( [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.11
    SUM ( [URP-Unconstrained Requirement Plan] ) - 0
        && SUM ( [SFRP- Plan de requisitos finales presentado] ) > 0
        && [CURP-Consolidated URP] > 0, DIVIDE (
        SUM ( [SFRP- Plan de requisitos finales presentado] ),
        SUM ( [CURP-Consolidated URP] )
    ),
    Condición No.13
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] )
    ) á 0
        && SUM ( [SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( [CURP-Consolidated URP] ) ? 0, DIVIDE ( SUM ( [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.14
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
    ) á 0
        && SUM ( [SFRP- Plan de requisitos finales presentado] ) > 0
        && SUM ( [URP-Unconstrained Requirement Plan] ) ? 0, DIVIDE ( SUM ( [SFRP- Submitted Final Requirement Plan] ), 100 ),
    Condición No.15 y Condición No.16 y Condición No.17
    CALCULAR (
        COUNTROWS ( 'Datos' ),
        NOT ( ISBLANK ( 'Datos'[SFRP- Plan de requisitos finales presentado] ) )
    ) á 0
        && (
            SUMA ([URP consolidado CURP] ) 0
                || CALCULAR (
                    COUNTROWS ( 'Datos' ),
                    NOT ( ISBLANK ( 'Datos'[URP consolidado CURP] )
                ) á 0
        ), 0,
    Condición No.18
    SUM ( [SFRP- Plan de requisitos finales presentado] ) - 0, 0,
    Condición predeterminada
    DIVIDE (
        SUM ( [SFRP- Plan de requisitos finales presentado] ),
        SUM ( [CURP-Consolidated URP] ),
        0 )
)

Saludos

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

Nivel de fila Cálculo porcentual para cada línea que tiene 9 condiciones.

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024