Forum Discussion

rawiswarden's avatar
rawiswarden
Helper I
6 years ago
Solved

Average Daily Count

Hello,   I have read a lot of threads on calculating an average daily count.  But I cannot get it right.  Any help is appreciated 🙂   I have a table that contains a daily count.   I am trying to...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    I recreated this sort of. See attached, but I create a Date table and then a sample table with the missing date in it. You will need to filter your VAR table for NOT(ISBLANK()). Here are the measures I created for testing:

     

    Measure 6 = COUNTROWS(RELATEDTABLE(Table11))

Measure 7 = 
    VAR __Table = ADDCOLUMNS(ALLSELECTED('Table11Date'),"Measure",[Measure 6])
RETURN
    COUNTROWS(FILTER(__Table,NOT(ISBLANK([Measure]))))

Measure 8 = 
    VAR __Table = ADDCOLUMNS(ALLSELECTED('Table11Date'),"Measure",[Measure 6])
RETURN
    AVERAGEX(FILTER(__Table,NOT(ISBLANK([Measure]))),[Measure])
rawiswarden's avatar
rawiswarden
Helper I
6 years ago

This data comes from a temporal table.  Sometimes errors in the ETL process occurs, so we do not have data for that day.  It should be rare that it happens, but it does happen.  I will try your suggestion 🙂

 

 

amitchandak's avatar
amitchandak
Super User
6 years ago

If you need to do average after grouping data (Count of data) , values can come handy

 

Count_measure =  You have already defined

 

 

Daily Average = AVERAGEX(values(Date[date]),[count_measure])

 

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601