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 actually a pretty simple calculation; I need at the end of the Grand total for each category, the average total for each.  So as the examples show,  the Tables category has a grand total of 28 over a 10 month period so I need the average to be 2.8.  However, there are some months where this is blank (I createad a measure to assign a 0 value in those cases) but Power BI won't include the blank months in the calculation.  In my example Power BI shows the average as 5.6.

 

I'm hoping someone can un-dumb my brain please!  Thanks in advance for your help.

 

 

Need Help

10 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Here are the different measures I have:

      1)  COUNT OF CATEGORY:  IF(COUNT('MyTable'[CATEGORY])=Blank(), 0, COUNT('MyTable',[CATEGORY]) 

      2) NUMBEROFMONTHS: DISTINCTCOUNT(Date_Dim(MONTHNAME_YEAR)

      3) GRAND TOTAL OF CATEGORIES:  SUMX('MyTable',[COUNT OF CATEGORY]

      4) AVERAGE:  CALCULATE ([GRAND TOTAL OF CATEGORIES]/NUMBEROFMONTHS)

       

      The AVERAGE measure works if there's a 'true' value for that month/year.  It seems when I artificially add that 0 value (in the COUNT OF CATEGORY measure) it still ignores it.

       

      Thanks!

  • SOppeneer's avatar
    SOppeneer
    Frequent Visitor
    3 years ago

    I know the post is very old (2017), but I stumbled upon it when looking into the same issue.

     

    The difference between Excel and Power BI is that Power BI ignores blanks and zeros when calculating the average while Excel does not. There might be a better way, but I was able to solve it by manually calculating the average as the sum of values divided by the count of elements. 

     

    Note: The column you use for the count should not include blanks, otherwise you need to ensure Power BI also counts the blanks (e.g. COUNT + COUNTBLANK). Then in Power BI it would become: SUM([values]) / ( COUNT([values]) + COUNTBLANK([values]) ).

     

  • 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.