Solved: Re: Row level Percentage calculation for each line... - Page 2

harirao · ‎05-14-2020

Hi All,

I want to calculated diffrence %, by giving 9 condition mentioned below for each row level calculation.

S.No	CURP	URP	SFRP	Difference %	Dax for Each row level calculation
1				N/A	IF All three columns are blank
2	100		90	(90/100)%	SFRP/CURP. When URP is Blank
3		50	90	(90/50)%	SFRP/URP. When CURP is Blank
4			90	(90/1)%	SFRP/1. When CURP & URP are Blank
5	100			(1/100)%	1/CURP. When SFRP & URP are Blank
6		50		(1/50)%	1/URP. When SFRP & CURP are Blank
7	100	100	100	(100/100)% = 0%	Should be 0% when all having same value
8	0	100	50	50%	When CURP is 0 consider SFRP value as Percentage
9	0	0	50	50%	When CURP & URP are 0, consider SFRP value as Percentage
10	0	0	0	0%	When CURP, URP & SFRP are 0, consider value as Percentage

Please check and correct me if below DAX is correct for Condition 2 to 6 which i tryed.
Diffirent% =

//Condition-2

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

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

//Condition-3

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

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

//Condition-4

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

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

//Condition-5

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

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

//condition-6

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

DIVIDE(1,'DATA'[URP-Unconstrained Requirement Plan],0))))))
I was not able to give condition1, where all three rows are blank is NA, here all three columns are in Format "Whole Number"
Please help me on remaining conditions also in single DAX.

PBI working

Regards,
Hari

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

Hi @harirao ,

We can try to create a measure to meet your requirement:

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
)

If you perfer a calculated column, please try to use the following,

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
)

Pleas note that if there are multi rows for each product ID and Location, the result of measure and column might be different.

such as following

ID	Location	CUPR	UPR	SFPR	%Difference Column
1	A			50	50%
1	A	50			2%
1	A		50		2%

But when in table visual:

ID	Location	CUPR	UPR	SFPR	%Difference Measure
1	A	50	50	50	0%

If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that you have shared?

Best regards,

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

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

Hi @harirao ,

For the measure, use the following:

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

For the same number issue, we think it may because it it a decimel number but display as whole number.

For 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
    )
)

For measure:

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

Best regards,

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

harirao · ‎05-22-2020

Hi @v-lid-msft ,

Thanks for your respone, a small update required from you
Condition # 3 is not working when SFRP is blank, default we have to consider it as 1 result should be (1/75=1%, 1/8=13%) but i am getting 0% that is wrong as per our conditions.

Regards,

Hari

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

Hi @harirao ,

Please use the following , we find that Condition 5 and Condition 3 is same, so we remove one.

For Column:

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 
    [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
    )
)

For measure:

Diffirent% Column =
SWITCH (
    TRUE (),
    //Condition No.1
    CALCULATE (
        COUNTROWS ( 'Data' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] ) )
    ) = 0
        && [CURP-Consolidated URP] > 0
        && SUM ( [SFRP- Submitted Final Requirement Plan] ) > 0, DIVIDE (
        SUM ( [SFRP- Submitted Final Requirement Plan] ),
        SUM ( [CURP-Consolidated URP] )
    ),
    //Condition No.2
    CALCULATE (
        COUNTROWS ( 'Data' ),
        NOT ( ISBLANK ( 'Data'[CURP-Consolidated URP] ) )
    ) = 0
        && SUM ( [URP-Unconstrained Requirement Plan] ) > 0
        && SUM ( [SFRP- Submitted Final Requirement Plan] ) > 0, DIVIDE (
        SUM ( [SFRP- Submitted Final Requirement Plan] ),
        SUM ( [URP-Unconstrained Requirement Plan] )
    ),
    //Condition No.3
    CALCULATE (
            COUNTROWS ( 'Data' ),
            NOT ( ISBLANK ( 'Data'[SFRP- Submitted Final Requirement Plan] ) )
        ) = 0
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, SUM ( [CURP-Consolidated URP] ) ),
    //Condition No.4
    CALCULATE (
        COUNTROWS ( 'Data' ),
        NOT ( ISBLANK ( 'Data'[CURP-Consolidated URP] ) )
    ) = 0
        && CALCULATE (
            COUNTROWS ( 'Data' ),
            NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] ) )
        ) = 0
        && SUM ( [SFRP- Submitted Final Requirement Plan] ) > 0, DIVIDE ( SUM ( [SFRP- Submitted Final Requirement Plan] ), 100 ),
    //Condition No.19
    SUM ( [CURP-Consolidated URP] ) = 0
        && SUM ( [URP-Unconstrained Requirement Plan] ) = 0
        && SUM ( [SFRP- Submitted Final Requirement Plan] ) > 0, DIVIDE ( SUM ( [SFRP- Submitted Final Requirement Plan] ), 100 ),
    //Condition No.6
    CALCULATE (
        COUNTROWS ( 'Data' ),
        NOT ( ISBLANK ( 'Data'[SFRP- Submitted Final Requirement Plan] ) )
    ) = 0
        && CALCULATE (
            COUNTROWS ( 'Data' ),
            NOT ( ISBLANK ( 'Data'[CURP-Consolidated URP] ) )
        ) = 0
        && SUM ( [URP-Unconstrained Requirement Plan] ) > 0, DIVIDE ( 1, SUM ( [URP-Unconstrained Requirement Plan] ) ),
    //Condition No.7
    CALCULATE (
        COUNTROWS ( 'Data' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] ) )
    ) = 0
        && CALCULATE (
            COUNTROWS ( 'Data' ),
            NOT ( ISBLANK ( 'Data'[SFRP- Submitted Final Requirement Plan] ) )
        ) = 0
        && CALCULATE (
            COUNTROWS ( 'Data' ),
            NOT ( ISBLANK ( 'Data'[CURP-Consolidated URP] ) )
        ) = 0, 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
    SUM ( [CURP-Consolidated URP] ) = 0
        && SUM ( [SFRP- Submitted Final Requirement Plan] ) > 0
        && SUM ( [URP-Unconstrained Requirement Plan] ) > 0, DIVIDE ( SUM ( [SFRP- Submitted Final Requirement Plan] ), 100 ),
    //Condition No.11
    SUM ( [URP-Unconstrained Requirement Plan] ) = 0
        && SUM ( [SFRP- Submitted Final Requirement Plan] ) > 0
        && [CURP-Consolidated URP] > 0, DIVIDE (
        SUM ( [SFRP- Submitted Final Requirement Plan] ),
        SUM ( [CURP-Consolidated URP] )
    ),
    //Condition No.13
    CALCULATE (
        COUNTROWS ( 'Data' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] ) )
    ) = 0
        && SUM ( [SFRP- Submitted Final Requirement Plan] ) > 0
        && SUM ( [CURP-Consolidated URP] ) = 0, DIVIDE ( SUM ( [SFRP- Submitted Final Requirement Plan] ), 100 ),
    //Condition No.14
    CALCULATE (
        COUNTROWS ( 'Data' ),
        NOT ( ISBLANK ( 'Data'[CURP-Consolidated URP] ) )
    ) = 0
        && SUM ( [SFRP- Submitted Final Requirement Plan] ) > 0
        && SUM ( [URP-Unconstrained Requirement Plan] ) = 0, DIVIDE ( SUM ( [SFRP- Submitted Final Requirement Plan] ), 100 ),
    //Condition No.15 & Condition No.16 & Condition No.17
    CALCULATE (
        COUNTROWS ( 'Data' ),
        NOT ( ISBLANK ( 'Data'[SFRP- Submitted Final Requirement Plan] ) )
    ) = 0
        && (
            SUM ( [CURP-Consolidated URP] ) = 0
                || CALCULATE (
                    COUNTROWS ( 'Data' ),
                    NOT ( ISBLANK ( 'Data'[CURP-Consolidated URP] ) )
                ) = 0
        ), 0,
    //Condition No.18
    SUM ( [SFRP- Submitted Final Requirement Plan] ) = 0, 0,
    //Default Condition
    DIVIDE (
        SUM ( [SFRP- Submitted Final Requirement Plan] ),
        SUM ( [CURP-Consolidated URP] ),
        0
    )
)

Best regards,

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-26-2020

Hi @v-lid-msft

After implementing, above deference% Column, few condition are not calculating correctly

Product ID	CURP	URP	SFRP	Condition -8	Should be 0% when all having same value
863180-001	7	7	7	0%
866987-001	1	1	1	0%
863180-001	6	6	6	0%
866987-001	1	1	1	0%

Product ID	CURP	URP	SFRP	Condition -1	DEFAULT: SFRP/CURP
863285-001	1	1	5	500%
866974-001	1	1	3	300%
863285-001	1	1	5	500%
866974-001	1	1	3	300%

Can you please help me on the above.

Note: I am unable use the measure, as getting below error in condition 1, 3, 8, 9 & 11

Thanks & Regards,
Hari

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

Hi @harirao ,

For the measure, use the following:

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

For the same number issue, we think it may because it it a decimel number but display as whole number.

For 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
    )
)

For measure:

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

Best regards,

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-27-2020

Hi @v-lid-msft,
Thanks for your reply, i want to create another measure/column for Difference (highlighted in blue), as mentioned below.
can you please help me on this using same 18 conditions.

Regards,
Hari

harirao · ‎05-29-2020

Hi @v-lid-msft,

Can you please help me on the above fixing issues on few conditions

Regards,

Hari

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

Hi @harirao ,

It seems like a new requirement, we would suggest you post it in a new thread, it would make others easier to focus on one question in one single thread and it will benefit other community members who stuck with the same requirement.

Best regards,

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-27-2020

Hi @v-lid-msft ,

Can you please help me on the above fixing issues on few conditions

Regards,

Hari

harirao · ‎05-17-2020

Hi v-lid-msft,

Thanks for your response, still getting few blanks, so I have reworked on conditions add few more, for row levle calculations required Result as per below table
Can you please help on the DAX calculation:

Regards,

Hari

harirao · ‎05-17-2020

Hi v-lid-msft,
Required your help for calculating this DAX.

Thanks & Regards,

Hari

harirao · ‎05-15-2020

Hi v-lid-msft,
Can you please help me on the below calculation.
Regards,

Hari

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

Hi @harirao ,

Please try to use the following formula:

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] )
        && 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] )
        || [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
)

Best regards,

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-14-2020

Hi amitchandak,

I want to create a new column as Difference% by using below 10 condition row level, i was able to create 2 to 6 conditions,
Condition: 1, 7, 8, 9 & 10 i am not able to do?

S.No	CURP	URP	SFRP	Difference %	Dax for Each row level calculation
1				N/A	IF All three columns are blank
2	100		90	(90/100)%	SFRP/CURP. When URP is Blank
3		50	90	(90/50)%	SFRP/URP. When CURP is Blank
4			90	(90/1)%	SFRP/1. When CURP & URP are Blank
5	100			(1/100)%	1/CURP. When SFRP & URP are Blank
6		50		(1/50)%	1/URP. When SFRP & CURP are Blank
7	100	100	100	(100/100)% = 0%	Should be 0% when all having same value
8	0	100	50	50%	When CURP is 0 consider SFRP value as Percentage
9	0	0	50	50%	When CURP & URP are 0, consider SFRP value as Percentage
10	0	0	0	0%	When CURP, URP & SFRP are 0, consider value as Percentage

Can you please help me on this

Regards,

Hari

Row level Percentage calculation for each line having 9 condition.

Helpful resources

Power BI Monthly Update - November 2025

Fabric Data Days

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

FabCon is coming to Atlanta

Row level Percentage calculation for each line having 9 condition.

Helpful resources

Power BI Monthly Update - November 2025

Fabric Data Days

FabCon Atlanta 2026

How to Get Your Question Answered Quickly