Average Daily Sales Over Entire Filtered Time Period

[Anonymous]

Hello all! 

I'm looking for help on a formula that averages daily units sold over a specified time period. Below is what I have been using through searching this board, and I thought it had been accurate, but I've just realized that it's not exactly what I need. What this seems to be calculating is average units over the filtered timeframe for ONLY the days that product sold. I discovered this because over the 4 day period I was looking at, one store sold 22 units on one of those days, and none on any of the other days, and it was giving me average units sold of 22, where I would expect it to yield 5.5. How can I get average units sold over the entire timeframe? Thank you! 

Previous Measure Used:

Average Units Sold =

AVERAGEX(

    KEEPFILTERS(VALUES('Sales'[Date])),

    CALCULATE(SUM('Sales'[Units Sold]))

)

[Ashish_Mathur · Accepted solution]

Hi,

You should first build a Calendar Table and then a relationship from the Date column of your Sales Table to the Date column of your Calendar Table.  Build your slicer from the Date column of the Calendar Table.  Write this measure

=SUM('Sales'[Units Sold])/COUNTROWS(Calendar)

Hope this helps.