cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ukeasyproj Member
Member

show items with no data as 0 instead of blanks

temp 1.PNG

 

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)

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Vvelarde Super Contributor
Super Contributor

Re: show items with no data as 0 instead of blanks

@ukeasyproj

 

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

 

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

  




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Sean Super Contributor
Super Contributor

Re: show items with no data as 0 instead of blanks

Even easier would be to add zero to your Measure

Then you don't need an IF statement to check if the expression returns BLANK ( )

So if your SUMX Measure works get rid of the IF statement and just add " + 0  "at the end

same with if you use any other expression (something like this for example)

Measure = CALCULATE ( SUM (table[column] ), FILTER (... ) ) + 0 

This will ensure you get a 0 when its blank!

Good Luck! Smiley Happy

21 REPLIES 21
Highlighted
Vvelarde Super Contributor
Super Contributor

Re: show items with no data as 0 instead of blanks

@ukeasyproj

 

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

 

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

  




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Sean Super Contributor
Super Contributor

Re: show items with no data as 0 instead of blanks

Even easier would be to add zero to your Measure

Then you don't need an IF statement to check if the expression returns BLANK ( )

So if your SUMX Measure works get rid of the IF statement and just add " + 0  "at the end

same with if you use any other expression (something like this for example)

Measure = CALCULATE ( SUM (table[column] ), FILTER (... ) ) + 0 

This will ensure you get a 0 when its blank!

Good Luck! Smiley Happy

SA_NYC Regular Visitor
Regular Visitor

Re: show items with no data as 0 instead of blanks

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

srhoag Frequent Visitor
Frequent Visitor

Re: show items with no data as 0 instead of blanks

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!

MS_Customer Frequent Visitor
Frequent Visitor

Re: show items with no data as 0 instead of blanks

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 Regular Visitor
Regular Visitor

Re: show items with no data as 0 instead of blanks

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

tinkinc Frequent Visitor
Frequent Visitor

Re: show items with no data as 0 instead of blanks

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

dsalony Frequent Visitor
Frequent Visitor

Re: show items with no data as 0 instead of blanks

I have a slightly diffrerent issue... I have data where i am trying to caluculate a daily average over a range of days.  For spme of the items, they have data for every day in the range, but for others they do not... so for example, Buidling A has data for all 10 days of the period analyzed.  The simple daily average for Building A is SUM(Values)/10 days.  For Building B however, say it only has data for 5 of the days... to calculate the simple average for Buliding B over the entire 10 day range, I need to simply divide by 10 as well, but the results, after trying ~10 different methods, always divides by only the 5 days where Building B had data... how do I force the blank days to be counted as zeros?

jthomson New Contributor
New Contributor

Re: show items with no data as 0 instead of blanks

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 3,887 guests
Please welcome our newest community members: