How to exlude SUM of zero

[Masha]

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!

[Masha · Accepted solution]

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 

[Anonymous]

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.

[amitchandak]

@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])

[Masha]

@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