show items with no data as 0 instead of blanks

[ukeasyproj]

Just a little background on the above table:

Project Name, Category Name, and Portfolio Name are from a table called 'Projects"

Actual, Committed are from a table called 'Expenses'

Projects is a one to many relationship with Expenses

In the above scenario, the project "cancer test project 5" has no related records in the Expenses table, so it is showing as blank, is there a way to show 0 instead

I have tried doing the following in the Projects table, but it gave unexpected results:  Actual = IF(ISBLANK(SUMX(RELATEDTABLE('Expenses'), [Actual])) = FALSE(), SUMX(RELATEDTABLE('Expenses'), [Actual]), 0)

[Vvelarde · Accepted solution]

@ukeasyproj

Hi, please try with this Dax Formula in a measure:

ActualM =
IF (
    CALCULATE ( SUM ( Expenses[Actual] ) ) = BLANK (),
    0,
    CALCULATE ( SUM ( Expenses[Actual] ) )
)

[tonymaclaren]

Hi

First of all, dont use (values)/10 use DIVIDE(Values),10) or you could get a divide by zero error.

try:

My Measure :=
VAR Values =
    SUM ( 'table name'[Column name] )
RETURN
    COALESCE ( DIVIDE ( Values, 10 ), 0 )

This will avoid the possibility of divide by zero, which is good practice and give you a zero if there is no data

[sphillips22]

Do you have a copy of you DAX formula, by chance?

It sounds like you could do a count against the day column and use that as your denominator.

[jthomson]

Could you not look up the first and last days in your sample and then use datediff to get your denominator?

[Anonymous]

Hello, this doesnt work at all with my Power Pivot both examples. The cells stay blank which then distrupts my growth calculation 

[MS_Customer]

Having to write a measure everytime you want to add a field (count, sum, etc) to a card is a poor solution.  Not only do you have to create and mange the measures which provide virtually no value, but measures are a performance drain. 

I know the measures wouldn't cause much of a drain, but if you have a report that is struggling for performance, the last thing you want is to be creating several extra measures.

Microsoft should fix this.

[sphillips22]

Wow, just add zero! Such a simple and elegant solution. Very nice! Thank you!

[srhoag]

Doh!   Your solution was so simplistic that I totally missed that option.   That has saved me a lot of recoding and hair pulling.   Thank you!

[SA_NYC]

Oh wow, what an elegant solution. Wish I'd searched and found this first, before trying to figure out myself. Thanks!!