Replace blank/NULL values by 0

Hi! I’m creating a WBR visual with the Receive Accuracy metric. The logic for RA metric is= POs correctly received/total POs
There are some weeks where we don’t have any POs delivered in specific regions, so we have rows for that week with null values.
RA_wbr

I want to substitute those null values by ‘N/A’. I first tried to edit format data for null values, but it’s not working. Then I tried the following calculated field formula, but it gives me error and I’m not able to save it:

ra_formula1

However, I tried changing distinct_count({correct_rec})/distinct_count({purchase_order_number}) by ‘K’ as a test output and I could save it, but it’s not the output that I want:

Can anyone support please?

Thanks,
Cris

Hey crizar,
the problem is that you try to use a string and a number as output in your calculated field.
You could try to put the else statement in a tostring function but i am not sure if it will be enough.

If this won’t work: try to separate the calculation in an different calculated field and in the ifelse you use tostring for that field.
I can try to create some pics later if it would help you.
BR
Robert

1 Like

Thanks Robert for your answer.

This makes sense. But if I convert to string my output from the PO Receive Accuracy metric, the number will not be in percent and I can format it as it’s recognized as text. Any idea on how to fix that?

RA_QS

Quick and dirty: you could do concat(tostring(your calculation*100),‘%’)

2 Likes

This worked! Thank you so much!

Hi @crizar
could you pls solve the topic if @robert.eibers solution works.
BR

Sorry I thought I already did. Done now.
Thanks.

1 Like

Hello, I am trying to do the same to replace “black cells” with 0.00% to have a consistent format. Below is what the table looks like when I tried to do the - [concat(tostring(your calculation*100),‘%’).

Here is what I have in my calculated fields: concat(toString((sum(Defect)/sum(Defect+Clear))*100),‘%’)

Basically, I want all cells to have a format of X.XX%. Please help!

Hi @prazon
what if you round the value before converting into string?
BR

Checking if I understood your question correctly. Do you mean embed in the formula to round the value first? Below is the original format I have with a simple formula applied in calculated field - sum(Defect)/sum(Defect+Clear). As you can see, there are blank cells. I wanted to replace those blank cells with 0.00%. This is to have consistent formatting across the cells.

I am new to power BI/Query and I appreciate the help! :slight_smile: