Forum Discussion
Dealing with missing rows in underlying data, and adding 0 values
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 |
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
15 Replies
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))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.
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/547907Greg 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?
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?
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 - OK, what column are you using in the axis of your Line chart then?
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.
