Measure returning incorrect output

[vjnvinod]

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

[Fowmy]

@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]

@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?

[bcdobbs]

Can you supply a demo pbix file?

[vjnvinod]

@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]

@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"

[v-kkf-msft]

Hi @vjnvinod ,

In my example data, your formula can return "N/A". Please check if your data is aggregated causing the measure to return 0. For example, Id 4 has 4 rows of data, when I add Column1 into this visual (i.e. at Column1 level), the measure will return N/A, blank and 0. But if I don't add column1 (i.e. at Id level), the measure will return the value 1.14. This is because the context of measure changes, and it will calculate ( 6 + 2 + 0 ) / ( -3 + 0 + 5 + 5 ) = 1.14.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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