Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Calculate average and include blanks

Hi all, I can't figure out the correct DAX function to use for this calculation so hopefully someone can point me the right way.   Below is a screenshot of what I'm trying to achieve.  It's actual...
Need Help
ZEB's avatar
ZEB
Frequent Visitor
2 years ago

I have encountered a similar scenario where Power BI's AVERAGEX()  is excluding blank and nulls from average calculation. 

Average Opportunities = AVERAGEX(
                               KEEPFILTERS(ALL(CalendarTable[Week Start Date])),
                               [Opportunities]
)

 

How can I modify my measure to include these blank or null values when calculating the average?

I've shared the link to the pbix file for reference. Any guidance on this would be greatly appreciated.

Thank you!

 

 

Matrix Average.pbix

 

Ashish_Mathur dsalony SOppeneer Anonymous 

SOppeneer's avatar
SOppeneer
Frequent Visitor
2 years ago

ZEB The approach I took was manually taking the sum of values divided by the count of values and blank values, so something like: SUM([values]) / ( COUNT([values]) + COUNTBLANK([values]) ). This would be in your CALCULATE function with appropriate filters. I suppose in your case this would then be the sum of [Opportunities] divided by the appropriate number of days from your CalendarTable.