Average Daily sales problem

[deepguptaac]

Hi, 

I am currently facing a peculiar problem related to average daily sales calculation.  My data set looks like this.

I am calculating average daily sales by dividing total sales by the distinct number of stores opened. This gives me an accurate result for a day. 

However, for a week or month, the values come out to be inaccurate. Since in a month or week, some stores might be open for a particular day only and are counted by DISTINCTCOUNT DAX. So, now the total sales value for that period is being divided by a higher number of stores than the actual number of stores. This results in lower than actual average daily sales value.

Example of a result is as follows :

Kindly help. 

[v-jianboli-msft · Accepted solution]

Hi @deepguptaac ,

Please try:

Measure =
VAR _a =
    SUMMARIZE ( 'Sales', [Date] )
VAR _b =
    ADDCOLUMNS (
        _a,
        "NumberOfStore",
            CALCULATE (
                DISTINCTCOUNT ( Sales[Store No_] ),
                FILTER ( _a, [Date] = EARLIER ( Sales[Date] ) )
            ),
        "Sales",
            - CALCULATE (
                SUM ( Sales[Net Amount] ),
                FILTER ( _a, [Date] = EARLIER ( Sales[Date] ) )
            )
    )
RETURN
    AVERAGEX ( _b, [Sales] / [NumberOfStore] )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@deepguptaac , Can share the current measure.

Assume you want Avg of sum above day level

Averagex(Values(Date[Date]), calculate(Sum(Sales[Value])) )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[deepguptaac]

@amitchandak  Please find the link for sample data for your better understanding.

https://drive.google.com/file/d/1O-SLkkMSFLvmItREYRqGIBL_zu3BMMEN/view?usp=sharing 

Here in the result in the total value for the selected period Average daily sales (ADS) should be 6716. However as total distinct store for that periods are 88 which is resulting in incorrect ADS. Store count should be average value for that period.

Thank you.