Dealing with missing rows in underlying data, and adding 0 values

[Anonymous]

To put it simply our database is calculating 40 metrics for thousands of projects on a daily basis. Each row in the database has project ID, metric type, value, and date. To save on performance our database doesn't store rows if the metric calculated is 0, however, the metric being 0 is actually an important insight, and something we want to display in reports. 

The issue I'm having is that I'm unable to satisfactorily display the table visual, and specifically the totals row. I can use a measure to display 0 if the metric is missing, no problem. However, a really important piece of these metrics is displaying their average score across the projects which I use the totals row for, and this is where the crux of the issue lies.

If I use a measure to display 0, the average won't take into account those 0's added by the measure and simply display an average of the metrics which have values >0.

Would really really love some help on this, and you'd be my hero!

I have created these three simplified tables below to give you better feel for the issue. 

1. First table shows which data is stored in the database

2. Second table shows how I want to display data in report view

3. Third table shows how I have tried to solve the problem unsucccesfully

How underlying data comes in

Project ID	Metric	Date
1	10	May
3	123	May
5	12	May
6	62	May
1	15	June
2	60	June
5	23	June
6	13	June

How I want to display data with the average taking into account zeros

Project ID	Metric
1	12.5
2	30
3	61.5
4	0
5	17.5
6	37.5
Avg	26.5

How I have been displaying data with wrong average

Project ID	Metric
1	12.5
2	60
3	123
4	0 (added by measure)
5	17.5
6	37.5
Avg	39.75