Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

IF statement for Month to equal a specified %

Hello,

 

Here's my scenario. I have a table with a Month column showing January 2019, Feburary 2019, etc... I would like to write an If statement (or if you have something else in mind thats more effective) that shows if row has January then display 10.5%, if row has February then display 9%, if row has Month then display the specified %. Also I will be using the output for another calculation.. Thank you. 

Need Help
Show and Tell

8 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Use the SWITCH function. Below is some pseudocode:

     

    My Measure =
SWITCH (
    TRUE (),
    Table[Month] = "January", .01,
    Table[Month] = "February", .02
)

    Just keep adding the months and values as needed. Much easier to read and edit than 12 nested IF functions.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you edhans . I was able to complete this using a new column but not a measure. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous，

    You can create one measure just like the one in the below screenshot(assume that you have the similar Month column with text data type):

    Note: Replace the numerical part of the formula with the actual percentage

    Measure =

    SWITCH (

        LEFT ( MAX ( 'Months'[Month] ), LEN ( MAX ( 'Months'[Month] ) ) - 5 ),

        "January", 0.105,

        "February", 0.09,

        "March", 0.3,

        "April", 0.024,

        "May", 0.035,

        "June", 0.21,

        "July", 0.09,

        "August", 0.37,

        "September", 0.08,

        "October", 0.38,

        "November", 0.49,

        "December", 0.175

    )

     

    If the above measure formula is not applicable in your scenario, please provide the related table structure and sample data.

     

    Best Regards

    Rena

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous,

      I tried this way (

      SWITCH
      (LEFT(MAX('Shrinkage (Teleopti)'[** Month]),
      LEN( MAX('Shrinkage (Teleopti)'[** Month])) - 5)

      but received the following result:

      It's not accepting my screen shot for some reason.. for every month its giving me a result of 100%

       

       
       

      I believe my date column is the same as yours:

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

        If the field "Shrinkage (Teleopti)'[** Month]" is Text data type, it can get the correct result using my provided formula. Could you please provide your sample PBIX file if it is convenient? Then I can check where the problem is .

         

        Best Regards

        Rena

  • lbeneyze's avatar
    lbeneyze
    Icon for Advocate II rankAdvocate II
    6 years ago

    Good morning wjtsk1,

     

    Personnally, I prefer to solve this via Power Query. 

    I suggest you the following approach:

      - create a Date percentage table by entering data:
           Column 1: Month with "January 2019", "February 2019"

           Column 2: Percentage with 10.5%

      - use a merge query in Power Query to "Merge" column percentage based on column Date in your manual table with your transaction query. https://www.youtube.com/watch?v=qXH4WjCykLc

     

    Did I answer your question, please mark my post as a solution

    If you liked my solution, please give it a thumbs up.

    Thank you and keep up the good job!

     

    Kind regards,

    Lohic Beneyzet