Forum Discussion

jak8282's avatar
jak8282
Helper III
3 years ago

Calculated column not working

Hi,   For some reason my calculated column is no working - all values are a decimal number.   DAX is  TestRange70to79.9 = IF( AND( [Supplier Percentage Conv] >= 'Time Penalty'[Left...
Need Help
jak8282's avatar
jak8282
Helper III
3 years ago

Hi Dhairya ,

 

Sure,

 

Time Penalty

PercentagePenalty
90- 97.910%
80- 89.920%
70 - 79.930%
60- 69.940%
<6050%

 

Calculated Columns

DashPosition = SEARCH("-", TRIM('Time Penalty'[Percentage]), 1, BLANK())
LessThanPosition = SEARCH("<", TRIM('Time Penalty'[Percentage]), 1, BLANK())
LeftValue =
IF(
    NOT ISBLANK('Time Penalty'[DashPosition]),
    VALUE(TRIM(LEFT('Time Penalty'[Percentage], 'Time Penalty'[DashPosition] - 1))),
    BLANK()
)
 
right position = IF(
    NOT ISBLANK('Time Penalty'[DashPosition]),
    VALUE(TRIM(MID('Time Penalty'[Percentage], 'Time Penalty'[DashPosition] + 1, LEN('Time Penalty'[Percentage]) - 'Time Penalty'[DashPosition]))),
    IF(
        NOT ISBLANK('Time Penalty'[LessThanPosition]),
        VALUE(TRIM(MID('Time Penalty'[Percentage], 'Time Penalty'[LessThanPosition] + 1, LEN('Time Penalty'[Percentage]) - 'Time Penalty'[LessThanPosition]))),
        BLANK()
    )
)
 
TestRange70to79.9 =
IF(
   [Supplier Percentage Conv] >= 'Time Penalty'[LeftValue] &&
   [Supplier Percentage Conv] <= 'Time Penalty'[right position],
    "Yes",
    "No"
)
 
and the measure
 
 [Supplier Percentage Conv] = 77.16
 
  • Dhairya's avatar
    Dhairya
    Solution Supplier
    3 years ago

    I tried your code it is giving perfect output.

  • jak8282's avatar
    jak8282
    Helper III
    3 years ago

    Hi Dhairya ,

     

    Thats interesting - I hardcoded the 77.16

     

    In the PBI it is using

     

    Supplier Percentage Conv = [Supplier Percentage]*100
     
    which uses
     
    Supplier Percentage = divide([WFM - Supplier Working Time],[WFM - Supplier Scheduled Time])
     
    • Dhairya's avatar
      Dhairya
      Solution Supplier
      3 years ago

      Though it is hard-coded, I think it should work with measures also.
      and instead of creating this measure Supplier Percentage Conv = [Supplier Percentage]*100
      you can format your previous measure as a percentage