Show 0 instead of blank

[markblom]

Hi,

I would like to show 0's instead of blanks in a table showing sales and purchases for products. I have created a dummy report to show what issue I am running into. 

The dummy report has 3 tables:

• Products: Product
• Sales: Product, Date, Quantity
• Purchases: Product, Date, Quantity

I have created to measures:

• Sum of Purchases = CALCULATE(SUM(Purchases[Quantity]))
• Sum of Sales = CALCULATE(SUM(Sales[Quantity]))

I created a table with three columns:

• Product
• Sum of Purchases
• Sum of Sales

Below you will find a screenshot of the outcome:

In the middle table only the products are shown that have a purchase of sales. But for product C, the sales column is blank. Here, I would like to display 0.

I then tried another measure:

• Sum of Sales Zero = CALCULATE(SUM(Sales[Quantity])) + 0

The table on the right show the outcome with that column. Now it shows 0's but now all products are shown in the table.

What can I do to only show products that either have sales or purchases with the value 0 instead of a blank?

Regards,

Mark

[markblom]

Thanks, that works!

[Greg_Deckler · Accepted solution]

@markblom Try:

Sum of Sales Zero =
  VAR __Result = IF( [Sum of Purchases] = BLANK() && [Sum of Sales] = BLANK(), BLANK(), SUM(Sales[Quantity]) + 0
RETURN
  __Result

Also, there is no reason for your CALCULATE statements wrapping your SUM functions