Zero does not show up in my visuals

[aspitalnick]

In several cells I have a zero value for one of my items. I've added that field to a matrix, a clustered column chart and an advanced card. I tried it as both a sum and an average, and for the numbers greater than zero it works perfectly, but for the zero values it always returns a blank instead of a zero. For purposes of this visual, a blank would mean something different than a zero. How do I get it to show a zero if there is a zero in the excel spreadsheet it is pulling from and a blank if no information is in the cell.

[Anonymous · Accepted solution]

Hi @aspitalnick 

Thank you very much Kaviraj11 for your prompt reply. Allow me to add a few methods.

Here's some dummy data

“Table”

Here are a few ways you can try:

Create measures.

Method 1 = 
var _sumQuantity = SUM('Table'[Quantity])
RETURN 
IF(
    ISBLANK(_sumQuantity),
    0,
    _sumQuantity
)

Method 2 = COALESCE(SUM('Table'[Quantity]), 0)

Method 3 = SUM('Table'[Quantity]) + 0

Here is the result.

Regards,

Nono Chen

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

[aspitalnick]

When I try 

CTOPP2 RS Fix = SUM('CTOPP2 4-6'[Rapid Symbolic Naming P-tile] + 0, it puts a zero in all rows, even the blank ones which is not what I was hoping for. I want the fields that are currently total zero in the spreadsheet to show zero on the visuals but leave the blank fields as blanks.

[Anonymous]

Hi @aspitalnick 

In order to better help you solve the problem, please provide sample data that fully covers your problem and the expected results based on the sample data you provided.

Regards,

Nono Chen

[Kaviraj11]

Hi,

You can create a measure:

NewMeasure = IF(ISBLANK(SUM(Table[Field])), BLANK(), IF(SUM(Table[Field]) = 0, 0, SUM(Table[Field])))

[aspitalnick]

This method did not work, but thank you for your idea.