Forum Discussion
Dealing with missing rows in underlying data, and adding 0 values
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
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?
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?
Okay I'll try that out!
I have date as the x-axis
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.