Forum Discussion

vjnvinod's avatar
vjnvinod
Impactful Individual
4 years ago

Measure returning incorrect output

hi,

 

Anonymous 

 

I have a measure like below

 

EU FWD Week 3 % =
VAR FWU =IF(SUM ( Mercury_Extract[Flexi EAH] )>0,
CALCULATE (
DIVIDE (
SUM ( 'Mercury_Extract'[Chargeble considering Leavers] ),
SUM ( Mercury_Extract[Flexi EAH] )
),
Week_Tbl[Visibility9] = TRUE
),"N/A")
RETURN
IF ( ISBLANK ( FWU ), BLANK(), FWU )
 
It returning 0.00 instead of N/A, not sure what is wrong with the formula
 

 

Need Help
Show and Tell

6 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    vjnvinod 

    The TRUE part of the IF function returns zero in this case, check your data and validate it. 
    Using "N/A" is not a good practice as it is a text where as your true part of the formula returns numbers. 


    • vjnvinod's avatar
      vjnvinod
      Impactful Individual
      4 years ago

      Fowmy 

       

      I changed the formula to like below

       

      EU FWD Week 3 % =
      Var NA =CALCULATE( IF(SUM(Mercury_Extract[Flexi EAH])<0,2,""),Week_Tbl[Visibility9])
      VAR FWU =
      CALCULATE (
      DIVIDE (
      SUM ( 'Mercury_Extract'[Chargeble considering Leavers] ),
      SUM ( Mercury_Extract[Flexi EAH] )
      ),
      Week_Tbl[Visibility9])
      RETURN
      IF(2,NA,FWU)
      which works, but the problem is that instead of number (2), i want to show its as N/A in my use case
       
      how do i tweak this formula?
    • vjnvinod's avatar
      vjnvinod
      Impactful Individual
      4 years ago

      bcdobbs 

       

      instead of NA, if I call the variable with a number, it works. I think it's not picking up the text

      for example, now I have used the below DAX

      how do i make this 2 TO "n/a"?

       

      EU FWD Week 3 % =
      Var NA =CALCULATE( IF(SUM(Mercury_Extract[Flexi EAH])<0,2,""),Week_Tbl[Visibility9])
      VAR FWU =
      CALCULATE (
      DIVIDE (
      SUM ( 'Mercury_Extract'[Chargeble considering Leavers] ),
      SUM ( Mercury_Extract[Flexi EAH] )
      ),
      Week_Tbl[Visibility9])
      RETURN
      IF(2,NA,FWU)

       

       

      • vjnvinod's avatar
        vjnvinod
        Impactful Individual
        4 years ago

        amitchandak 

        Hi Amit, Can you help?

        I think it's a small change I need in the below code.Instead of number, i need a text like "n'a"