topic Re: How to exlude SUM of zero in DAX Commands and Tips

How to exlude SUM of zero

Masha — Wed, 23 Jun 2021 02:55:31 GMT

Hi,

Here is a measure SUM (0+0+0+0.6+0.6+0.6...) divided by COUNT of dates.

The question is how to exclude those dates when summary equals 0.00 from this calculations?

Currently it calculates: 4.2 / 12 = 0.35

I need it to calculate: 4.2 / 7 = 0.6

the reason 0.00 is showing for some dates is because in the data there is 0.6 but later on there was another data added with -0.6 for those dates. Hence below we see the summary.

Thank you!

Re: How to exlude SUM of zero

amitchandak — Wed, 23 Jun 2021 04:55:56 GMT

Masha , Have you used show item with no data, because I can see blank too

Usually, we do like

if([Meausre] =0.0 ,blank(), [measure])

Re: How to exlude SUM of zero

Masha — Wed, 23 Jun 2021 05:52:35 GMT

amitchandak

yes, but sadly it doesn't work, because in the data it is 0.6-0.6 and in the report it's displayed as 0.00

Re: How to exlude SUM of zero

Anonymous — Wed, 23 Jun 2021 06:11:34 GMT

Hi,

What about creating several measures:

1 measure to calculate the sum for the data, such as

Total Data:= SUM(table[Data])

1 measure to count the number of total greater than 0, such as

Not Null Count := COUNTX('Calendar'[Date], [Total Data] >0)

1 measures to do the division:

Average Data:= DIVIDE([Total Data], [Not Null Count])

I think that should work if my assumption are correct:

You have a date table,
You use the measure average data in your matrix.

Re: How to exlude SUM of zero

Masha — Thu, 24 Jun 2021 10:58:33 GMT

Thank you all!

I could not make it work in DAX. I amended it at the data level: grouped and filtered out zero. That worked