# Replace blank/NULL values by 0 **URL:** \ **Category:** Q&A\ **Tags:** visuals, formatting, Business-Intelligence-Engineer, quick-sight\ **Created:** [January 22, 2024, 11:58am UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987 "2024-01-22T11:58:09Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![crizar](https://avatars.discourse-cdn.com/v4/letter/c/ecd19e/32.png) [@crizar](https://community.amazonquick.com/u/crizar)\ **Post date:** [January 22, 2024, 11:58am UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987/1 "2024-01-22T11:58:09Z")
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](https://d1wl4jav5dp96l.cloudfront.net/original/3X/8/d/8d137d88afb7970d3b381600d137d4009d5d31d0.png) 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](https://d1wl4jav5dp96l.cloudfront.net/original/3X/5/d/5ddfb2cf2594d08f3a0087bc3155e90706ad4bc7.png) 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: ![ra_formula2](https://d1wl4jav5dp96l.cloudfront.net/original/3X/9/7/9710e9a6d6025e9a41bb62e94f8854a972efd0d1.png) Can anyone support please? Thanks, Cris ---
**Author:** ![robert.eibers](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/robert.eibers/32/17657_2.png) [@robert.eibers](https://community.amazonquick.com/u/robert.eibers)\ **Post date:** [January 22, 2024, 12:29pm UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987/2 "2024-01-22T12:29:07Z")
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](https://docs.aws.amazon.com/quicksight/latest/user/toString-function.html) 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](https://docs.aws.amazon.com/quicksight/latest/user/toString-function.html) for that field. I can try to create some pics later if it would help you. BR Robert ---
**Author:** ![crizar](https://avatars.discourse-cdn.com/v4/letter/c/ecd19e/32.png) [@crizar](https://community.amazonquick.com/u/crizar)\ **Post date:** [January 23, 2024, 10:46am UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987/3 "2024-01-23T10:46:41Z")
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](https://d1wl4jav5dp96l.cloudfront.net/original/3X/6/7/67e947d7296432aea4531439d6a38b7afca573e5.png) ---
**Author:** ![robert.eibers](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/robert.eibers/32/17657_2.png) [@robert.eibers](https://community.amazonquick.com/u/robert.eibers)\ **Post date:** [January 24, 2024, 7:16am UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987/4 "2024-01-24T07:16:18Z")
Quick and dirty: you could do [concat(](https://docs.aws.amazon.com/quicksight/latest/user/concat-function.html)tostring(your calculation\*100),‘%’) ---
**Author:** ![crizar](https://avatars.discourse-cdn.com/v4/letter/c/ecd19e/32.png) [@crizar](https://community.amazonquick.com/u/crizar)\ **Post date:** [January 24, 2024, 9:09am UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987/5 "2024-01-24T09:09:32Z")
This worked! Thank you so much! ---
**Author:** ![ErikG](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/erikg/32/14524_2.png) [@ErikG](https://community.amazonquick.com/u/ErikG)\ **Post date:** [January 31, 2024, 9:09pm UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987/6 "2024-01-31T21:09:32Z")
Hi @crizar could you pls solve the topic if @robert.eibers solution works. BR ---
**Author:** ![crizar](https://avatars.discourse-cdn.com/v4/letter/c/ecd19e/32.png) [@crizar](https://community.amazonquick.com/u/crizar)\ **Post date:** [February 1, 2024, 8:07am UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987/7 "2024-02-01T08:07:45Z")
Sorry I thought I already did. Done now. Thanks. ---
**Author:** ![prazon](https://avatars.discourse-cdn.com/v4/letter/p/7bcc69/32.png) [@prazon](https://community.amazonquick.com/u/prazon)\ **Post date:** [February 8, 2024, 10:37pm UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987/8 "2024-02-08T22:37:18Z")
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),‘%’). ![Capture](https://d1wl4jav5dp96l.cloudfront.net/original/3X/0/9/09497410cbe9e2c6a31221832c2bf66fc2274a44.png) 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! ---
**Author:** ![ErikG](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/erikg/32/14524_2.png) [@ErikG](https://community.amazonquick.com/u/ErikG)\ **Post date:** [February 8, 2024, 11:46pm UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987/9 "2024-02-08T23:46:36Z")
Hi @prazon what if you round the value before converting into string? BR ---
**Author:** ![prazon](https://avatars.discourse-cdn.com/v4/letter/p/7bcc69/32.png) [@prazon](https://community.amazonquick.com/u/prazon)\ **Post date:** [February 9, 2024, 5:29pm UTC](https://community.amazonquick.com/t/replace-blank-null-values-by-0/23987/10 "2024-02-09T17:29:04Z")
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! 🙂 ![Capture](https://d1wl4jav5dp96l.cloudfront.net/original/3X/a/e/aedf606e89e9327bf9fb95f2b3bf94abeb7a2473.png)