Forum Discussion

nmckbcs's avatar
nmckbcs
Helper I
7 years ago

Average to include blank months

Hello,

 

is there a way that my formula can include blank months.. I am using the below formula and would like to be able to get an average of 19.06 instead of 27.24. However, Power BI will exclude any months with no values in the formula. Below you will notice there is no values for March and May because of low sample size; however, we do want that to be included in the average because when folks are looking at this data YTD they will compare it to the overall YTD average and it all should match.

 

Link to file:

https://drive.google.com/file/d/1siXptcM3FGuvQ3VH4qbC3aBdgFUa0Dgl/view?usp=sharing

 

AVG_Category_Breakdown =
AVERAGEX(SUMMARIZE(VALUES('download to use'[Month]),[Month],"ABCD",[Category_Breakdown]),[ABCD])
 
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

7 Replies

  • affan's avatar
    affan
    Solution Sage
    7 years ago

    Hi nmckbcs

     

    Can you consider sharing a sample from your 'download to use' table? This will help getting the solution fast.

     

     

    Regards

    Affan

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    nmckbcs

     

    Maybe use the query editor to replace Nulls (blanks) with zero?

    • nmckbcs's avatar
      nmckbcs
      Helper I
      7 years ago
      This may cause additional issues is my guess but it is an alternative I am considering if no one has a better idea. affan I added a link above in my original post.
      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        nmckbcs

        Perhaps SUM( ) divided by COUNTROWS( Table1) if Table1 has other columns with non-blanks in the rows where the column being averaged has blanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    You're not working with a date table. This causes you to have no blank data at the missing months because those missing months are not actually there for that category.

    • affan's avatar
      affan
      Solution Sage
      7 years ago

      nmckbcs

       

      What I believe is that you should create a dimension table. It can be date dimension table or only a single column with months. Then you can create relationship and show the months with 0 values.

      • nmck86's avatar
        nmck86
        Post Patron
        7 years ago

        Can you show me how I could accomplish this with the creation of a date table? The pbix is above. I have a date table in my original file set I just shorted this one down and provided dummy data to try and give a sampel file for assistance.