Forum Discussion

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

Row level Percentage & Difference calculation for each line wise

Hi Team,


I had earlier posted this question, i want to calculated row level percentage and difference, challenge was few conditions output should be in  String, but source columns are in number format. is there any way to get the below output. 



DAX supported by  @v-lid-msft lot.

Diffirent% =
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 ( [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.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] ), 0,
//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] )
), 0,
//Condition No.18
[Submitted Final Requirement Plan] = 0, 0,
//Default Condition
DIVIDE (
[Submitted Final Requirement Plan],
[Consolidated URP],0))

Required a solution where i can bring percentage as will as string in same (Difference Column)
Thank you.

 

Regards,

Hari

 

Need Help
Show and Tell
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi harirao ,

    To remove percentage decimal point, modify the formula like this:

    For example, I have added two columns and modified the default condition formula:

        //Default Condition
    FORMAT(DIVIDE (
        [Submitted Final Requirement Plan],
        [Consolidated URP],
        0
    ) * 100,"#") & "%"

     

    About condition 8, not add format() function in it and the previous formula should work unless there is some repeated judgment logic in it, you should check your formula in details and try it again.

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi harirao ,

    The reason that cause this issue is that the cardinality is too small, I have modified the default condition formula like this to avoid it:

        //Default Condition
    var _value = 
        DIVIDE (
            [Submitted Final Requirement Plan],
            [Consolidated URP],
            0
        ) * 100
    return
    IF(
        _value > 0 && _value < 1,
        FORMAT(_value, "0.#") & "%",       //"0%",  //   if you don't want to show 0% directly, just wrtie "0%" in this step
        FORMAT(_value,"#") & "%"
    )

    As I commented in the formula, if you want to show 0% directly, just write "0%" and it will work.

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI harirao ,

     

    You will need to use format function

    When a % is expected  use FORMAT('Table'[Value], "percent")

     

    e.g

    Column =
    SWITCH(
    True(),
    'Table'[Question] = "Home" , "Home",
    'Table'[Question] = "cafe" , FORMAT('Table'[Value], "percent")
    )
     
    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
    • harirao's avatar
      harirao
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Hi Anonymous,

      Can you please help me to implement same for the below DAX got confused.
      Thank you

      regards,

      Hari 

      Diffirent%_Condition=

      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 ( [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.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] ), 0,
      //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] )
      ), 0,
      //Condition No.18
      [Submitted Final Requirement Plan] = 0, 0,
      //Default Condition
      DIVIDE (
      [Submitted Final Requirement Plan],
      [Consolidated URP],
      0))

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi harirao ,

    When you create a calculated column, change the data type into text firstly, then I have modified your formula like this:

    Column =
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] ) * 100 & "%",
    //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)]
    ) * 100 & "%",
    //Condition No.3
    ISBLANK ( [Submitted Final Requirement Plan] )
        && [Consolidated URP] > 0, "Blank SFRP",
    //Condition No.4
    ISBLANK ( [Consolidated URP] )
        && ISBLANK ( [Unconstrained Requirement Plan (URP)] )
        && [Submitted Final Requirement Plan] > 0, "0%",
    //Condition No.19
    [Consolidated URP] = 0
        && [Unconstrained Requirement Plan (URP)] = 0
        && [Submitted Final Requirement Plan] > 0, DIVIDE ( [Submitted Final Requirement Plan], 100 ) * 100 & "%",
    //Condition No.6
    ISBLANK ( [Submitted Final Requirement Plan] )
        && ISBLANK ( [Consolidated URP] )
        && [Unconstrained Requirement Plan (URP)] > 0, "Blank SFRP",
    //Condition No.7
    ISBLANK ( [Consolidated URP] )
        && ISBLANK ( [Submitted Final Requirement Plan] )
        && ISBLANK ( [Unconstrained Requirement Plan (URP)] ), "N/A",
    //Condition No.8 & Condition No.9
    [Unconstrained Requirement Plan (URP)] = [Submitted Final Requirement Plan]
        && [Submitted Final Requirement Plan] = [Consolidated URP]
        && NOT ( ISBLANK ( [Consolidated URP] ) )
        && NOT ( ISBLANK ( [Submitted Final Requirement Plan] ) )
        && NOT ( ISBLANK ( [Unconstrained Requirement Plan (URP)] ) ), "0%",
    //Condition No.10
    [Consolidated URP] = 0
        && [Submitted Final Requirement Plan] > 0
        && [Unconstrained Requirement Plan (URP)] > 0, "0%",
    //Conditon No.11
    [Unconstrained Requirement Plan (URP)] = 0
        && [Submitted Final Requirement Plan] > 0
        && [Consolidated URP] > 0, DIVIDE ( [Submitted Final Requirement Plan], [Consolidated URP] ) * 100 & "%",
    //Condition No.13
    ISBLANK ( [Submitted Final Requirement Plan] )
        && [Unconstrained Requirement Plan (URP)] > 0
        && [Consolidated URP] = 0, "Blank SFRP",
    //Condition No.14
    ISBLANK ( [Unconstrained Requirement Plan (URP)] )
        && ISBLANK ( [Submitted Final Requirement Plan] )
        && [Consolidated URP] = 0, "Blank SFRP",
    //Condition No.15
    [Unconstrained Requirement Plan (URP)] = 0
        && ISBLANK ( [Consolidated URP] )
        && ISBLANK ( [Submitted Final Requirement Plan] ), "Blank SFRP",
    //Condition No.16
    [Submitted Final Requirement Plan] = 0
        && ISBLANK ( [Consolidated URP] )
        && ISBLANK ( [Unconstrained Requirement Plan (URP)] ), "0%",
    //Default Condition
    DIVIDE (
        [Submitted Final Requirement Plan],
        [Consolidated URP],
        0
    ) * 100 & "%"
)

     

    Sample file is attached, please check and try it: Row level Percentage & Difference calculation for each line wise.pbix 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

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

      Hi v-yingjl,
      Thanks for providing the solution it is working almost fine, for two things need your assistance,
      a) removing the decimals for percentage  & b) regarding condition 8 is not giving correct results it should  be 0%



      Thank you

      Regards,

      Hari 

      • dobregon's avatar
        dobregon
        Icon for Impactful Individual rankImpactful Individual
        6 years ago

        Hi harirao .

        To remove the decimals you can adapt the format.

        Format(table[column],"0%")

        Where you see the "0%" is to put the format without any decimals. if you use "0.0%" you will see one decimal, etc.

        Related to the condition 8, you have in the measure that the condition 8 and 9 are the same so, maybe you need to analyze some exmaples and correct the formula or create condition 8 and 9 separately