Forum Discussion

harirao's avatar
harirao
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Row level Percentage calculation for each line having 9 condition.

Hi All,

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

S.No CURPURPSFRPDifference %Dax for Each row level calculation 
1   N/AIF All three columns are blank
2100 90(90/100)%SFRP/CURP. When URP is Blank
3 5090(90/50)%SFRP/URP. When CURP is Blank
4  90(90/1)%SFRP/1. When CURP & URP are Blank
5100  (1/100)%1/CURP. When SFRP & URP are Blank
6 50 (1/50)%1/URP. When SFRP & CURP are Blank
7100100100(100/100)% = 0%Should be 0% when all having same value 
801005050%When CURP is 0 consider SFRP value as Percentage 
9005050%When CURP & URP are 0, consider SFRP value as Percentage 
100000%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

 

 

Interesting Links
Need Help
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

33 Replies

    • harirao's avatar
      harirao
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Hi 

       

      S.No CURPURPSFRPDifference %Dax for Each row level calculation 
      1   N/AIF All three columns are blank
      2100 90(90/100)%SFRP/CURP. When URP is Blank
      3 5090(90/50)%SFRP/URP. When CURP is Blank
      4  90(90/1)%SFRP/1. When CURP & URP are Blank
      5100  (1/100)%1/CURP. When SFRP & URP are Blank
      6 50 (1/50)%1/URP. When SFRP & CURP are Blank
      7100100100(100/100)% = 0%Should be 0% when all having same value 
      801005050%When CURP is 0 consider SFRP value as Percentage 
      9005050%When CURP & URP are 0, consider SFRP value as Percentage 
      100000%When CURP, URP & SFRP are 0, consider value as Percentage

    • harirao's avatar
      harirao
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Hi 
      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's avatar
        harirao
        Icon for Post Prodigy rankPost Prodigy
        6 years ago

        Hi All,

         

        Can someone provide me solution for this?

         

        Regards,

        Hari