Solved: Re: Row level Percentage calculation for each line...

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

amitchandak · ‎05-14-2020

@harirao , did not get completely. You can use switch true

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

harirao · ‎05-14-2020

Hi amitchandak,
I want to calculate a Percentage for the below table in row level, were i have to give 10 condition

Condition to be applied

Hope i have not confused now?

Regards,

Hari

harirao · ‎05-14-2020

Hi All,

Can someone provide me solution for this?

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.

harirao · ‎05-15-2020

Hi v-lid-msft,

Required few more inputs on Different % Column
1. Default % calculation is SFRP/CURP when URP is Blank or 0 Eg: (1109/710=156%), (48/3= 16%) & (277/103= 269%) but getting wrong result.
2. result is not showing (92/46= 2%) SFRP/CURP when URP is Blank or 0
3. when CURP is blank and URP is 0 Same SFRP should be in result should i.e 52% & 152% mentioned in below snapshot.

Regards,

Hari

harirao · ‎05-15-2020

Hi v-lid-msft,
One More thing wanted to know regarding Condition-5
When SFRP is Blank, consider CURP that is 1/CURP
Eg1: (1/38= 3%) but result is showing blank?
Eg2: (1/60=2%) but result is showing 0%

Can you please help me on this

Regards,

Hari

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

Hi @harirao ,

Please try to change the condition 5 such as 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] )
                || [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
)

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

Hi v-lid-msft,
Thanks for your response.

Made the below changes, still result is not accurate
Eg1: (214/161= 133%) condition3: SFRP/URP. When CURP is Blank.
Eg2: Percentage columns should not be blank as per condition 9-When CURP & URP are 0, consider SFRP value as Percentage.

Onemore i noticed is lots of Blanks rows appering, please help me on this too

Regards,

Hari

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

Hi @harirao ,

We move the condition 9 to the second as 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. 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
)

Could you please extend the conditions form in the reply 4 so that we can modify the formula? Please consider the blank and zero condition as well.

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

Hi v-lid-msft,

Can you please help me on this DAX calculation.

Regards,

Hari

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

Hi @harirao ,

Please check the following dax based on the shared excel:

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
)

You can expand it easily if you have more condition:

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

The earlier condition will be matched first and be calculated, if matched, following condition will not try to match.

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

Hi v-lid-msft,

Please note, blank are appering still and few conditions are not working correctly.
I. (SFRP/CURP) (92/2=4800%) & (214/161=133%)

S.No	CURP	URP	SFRP	Condition	Result
2		50	90	SFRP/URP. When CURP is Blank	(90/50)*100 = 180%

Below calculation is showing wrong.

Blanks should be replaced with SFRP Value as percentage.

CURP	URP	SFRP	Condition	Result
0	0	90	When CURP & SFRP are 0 consider SFRP value as Percentage	90%

Regards,

Hari

harirao · ‎05-19-2020

Hi v-lid-msft,

Please help me on the below calculation.

Regards,

Hari

harirao · ‎05-19-2020

Hi @v-lid-msft,

Required your help for calculating this DAX.

Thanks & Regards,
Hari

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

Hi @harirao ,

For the second screenshot, we can add a condition 19 to solve it:

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
)

But the row 1272% seems be combined by multi %Column, could you please check it in raw or use following Measure to verify it?

Verify = Count(Diffirent% Column)

Also be aware that because we cannot output N/A along with the number format, so in some condition it will output blank, such as Condition 7 and Condition 15-17

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

Hi @v-lid-msft,

Thanks for your response, Yes for 1272% seems be combined by multi %Column.
I have used same dax in my Main pbix file, still Main condition/defaultcondition SFRP/CURP is not working.
Eg: (15/5=300%) (20/42=48%) (244/267=91%) &(668/549=122%) etc..

Difference% =

SWITCH (

TRUE (),

//Condition No.1

ISBLANK ( [Unconstrained Requirement Plan (URP)] )

&& [Consolidated URP] > 0

&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], [Consolidated URP] ),

//Condition No.2

ISBLANK ( [Consolidated URP] )

&& [Unconstrained Requirement Plan (URP)] > 0

&& [Submitted Final Requirement Plan] > 0, DIVIDE (

[Submitted Final Requirement Plan],

[Unconstrained Requirement Plan (URP)]

),

//Condition No.3

ISBLANK ( [Unconstrained Requirement Plan (URP)] )

&& ISBLANK ( [Submitted Final Requirement Plan] )

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

//Condition No.4

ISBLANK ( [Consolidated URP] )

&& ISBLANK ( [Unconstrained Requirement Plan (URP)] )

&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),

//Condition No.19

[Consolidated URP] = 0

&& [Unconstrained Requirement Plan (URP)] = 0

&& [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),

//Condition No.5

ISBLANK ( [Unconstrained Requirement Plan (URP)] )

&& ISBLANK ( [Submitted Final Requirement Plan] )

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

//Condition No.6

ISBLANK ( [Submitted Final Requirement Plan] )

&& ISBLANK ( [Consolidated URP] )

&& [Unconstrained Requirement Plan (URP)] > 0, DIVIDE ( 1, [Unconstrained Requirement Plan (URP)] ),

//Condition No.7

ISBLANK ( [Unconstrained Requirement Plan (URP)] )

&& ISBLANK ( [Submitted Final Requirement Plan] )

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

//Condition No.8 & Condition No.9

[Unconstrained Requirement Plan (URP)] = [Submitted Final Requirement Plan]

&& [Submitted Final Requirement Plan] = [Consolidated URP], 0,

//Condition No.10 & Condition No.12

[Consolidated URP] = 0

&& [Submitted Final Requirement Plan] > 0

&& [Unconstrained Requirement Plan (URP)] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),

//Condition No.11

[Unconstrained Requirement Plan (URP)] = 0

&& [Submitted Final Requirement Plan] > 0

&& [Consolidated URP] > 0, DIVIDE ( [Submitted Final Requirement Plan], [Consolidated URP] ),

//Condition No.13

ISBLANK ( [Unconstrained Requirement Plan (URP)] )

&& [Submitted Final Requirement Plan] > 0

&& [Consolidated URP] = 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),

//Condition No.14

ISBLANK ( [Consolidated URP] )

&& [Submitted Final Requirement Plan] > 0

&& [Unconstrained Requirement Plan (URP)] = 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ),

//Condition No.15 & Condition No.16 & Condition No.17

ISBLANK ( [Submitted Final Requirement Plan] )

&& (

[Consolidated URP] = 0

|| ISBLANK ( [Consolidated URP] )

), BLANK (),

//Condition No.18

[Submitted Final Requirement Plan] = 0, 0

)

Verify = Count(Difference%)

If above condition satisfy Deafult (SFRP/CURP) remainig as "0" , as we have number format

Thank You.

Regards,
Hari

harirao · ‎05-21-2020

Hi @v-lid-msft
Can you please help me on this DAX calculation.

Regards,

Hari

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

Hi @harirao ,

Please refer to following dax, default conditon is S/C, and N/A will show as 0 instead of blank

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

But for the multi raw rows condition, we think it use measure will be better, please refer to the reply 6 in this thread to learn more about this situation. we will transform this calculated column into measure in few days.

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

Hi @v-lid-msft,

Thanks for all your help, %Colum is working correctly.

Regards,

Hari

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

Hi @harirao ,

If you want a measure solution (in case one row in table visual contain multi raw rows), please refer to following, changet he 'Data' to the name of your table:

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'[URP-Unconstrained Requirement Plan] ) )
    ) = 0
        && 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.5
    CALCULATE (
        COUNTROWS ( 'Data' ),
        NOT ( ISBLANK ( 'Data'[URP-Unconstrained Requirement Plan] ) )
    ) = 0
        && CALCULATE (
            COUNTROWS ( 'Data' ),
            NOT ( ISBLANK ( 'Data'[SFRP- Submitted Final Requirement Plan] ) )
        ) = 0
        && [CURP-Consolidated URP] > 0, DIVIDE ( 1, SUM ( [CURP-Consolidated URP] ) ),
    //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.

Row level Percentage calculation for each line having 9 condition.

Helpful resources

FabCon Global Hackathon

Power BI Monthly Update - October 2025

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

FabCon Global Hackathon

Power BI Monthly Update - October 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly