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

[Greg_Deckler]

@Anonymous - OK, what column are you using in the axis of your Line chart then?

[Anonymous]

I'm using the date column, more specifically the date hierarchy with months, and years. 

[Greg_Deckler]

@Anonymous - OK, can you post the measure formulas that we created? I should be able to tell what is going haywire if I have those.

[Anonymous]

Sure thing!

Measure 13 = var testmeasure1 = AVERAGE('CSDB project_metric'[Work at Height])

RETURN

IF(ISBLANK(testmeasure1),0,testmeasure1)

Measure 14 =

VAR TABLE1 = SUMMARIZE('CSDB project','CSDB project'[id],"Work at height test",[Measure 13])

return

if(HASONEVALUE('CSDB project'[id]),[Measure 13],AVERAGEX(TABLE1,[Work at height test]))

[Anonymous]

@Greg_Deckler 

Any luck with this? Is there anything else I can provide?

Again, your help is greatly, greatly appreciated. 

[Greg_Deckler · Accepted solution]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

@Greg_Deckler 

Greg thanks for your excellent help here, you have truly solved the issue as outlined in the post. For anyone reading the solution lies in Greg's link https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

However, I have run into a bit of a complication that I haven't been able to crack. Creating the measure where you create a synthetic table, and use the averages for the rows of that table works well for the table visual. However, it doesn't seem to work for the card visual or the line chart visual. 

Do you have any idea why that could be?

[Greg_Deckler]

@Anonymous - What is the axis of your Line chart? Can you use the m_single version of the measure instead of the m_total version of the measure?

[Anonymous]

I tried the m_single version of the measure and it returns the wrong average. It actually returns the same average as the default row totals average does. So it is not taking into account the rows with a zero value in the average calculation. 

[Anonymous]

Okay I'll try that out!

I have date as the x-axis

[mahoneypat]

Have you tried this approach?

Project Avg = AVERAGEX(VALUES(Table[Project ID]), [Metric])

If your metric measure returns zero, it would be included in the average.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

I have tried that approach, and it works on the project row level. But it doesn't produce the desired result for the totals row (where we want an average of averages)

[lbendlin]

You need a master table for your projects

After that it is trivial.  In your visual select the project ID from the master table and the metric from the results table. Show items wth no data.

And the measure will be 

Measure = divide(sum(Results[Metric]),COUNTROWS(Projects))

[Anonymous]

Thanks for your reply.

So I have a master table for my projects, but I realize there is perhaps some more complexity I need to add in this for you.

In the table visual I'm looking at the average of the project metric over time for each individual project, and I have a filter to select time in the report. So the table I shared with you earlier with the metrics, each row there is an average over time for a particular project.  I have built a filter so the user can select time frame. Some projects will have data for the entire time frame, while others will not have data for the entire time, so therefore I believe I can't simply divide the sum of the metric by the project count.

[Anonymous]

I have updated the tables in my OP to better explain the complexity