Avg Sales per day - based on different days of work

[madnanansari]

I am working on Retail Sales data.

In some cities the stores works 7 days whereas in some they work 6 days a week.

So when I do an average sales per day for a month calculation it should take into account the operational days to calculate.  Let say for the month of January 2018; for Cities where the stores are closed on Sunday, the number of days is 27 whereas for other cities the number of days is 31.

I am using standard master calendar.

The below are the table formats:

Store:

Store

Store City

City Operational Days:

City

Operational Day. (Sat, Sun, Mon etc)

Sales:

Invoice No

Item

Store

Qty

Sales

I need the average Sales Per day

[swuehl]

I would expect to see a SalesDate field in Sales table.

If your Sales table show such a field, do you really have entries for a store on a date without sales?

If not, do you have an entry for each store on each sales date with either a value >0 or zero (which I assume is kind of unusual, to have no sales at all on a given date, but this might depend on your stores & business). In other words, are there any cases with no records for a store but operational on that date?

If your sales table shows a record per store and operational date, you should basically be fine.

Use something like

=Sum(Sales) / Count({<Store = {"*"}>} DISTINCT Date)

assuming a Store based dimension.

[madnanansari]

Sales table have entries of sales. Not without sales. Neither it has entries for each date.

This will not work.

If u can play something with the master data? where its defined which cities are operating on which days.

[swuehl]

This means there is no time dimension in your sales table? How do you aggregate monthly sales value then?

[madnanansari]

Date is there...but with figures for sales not empty lines.

[madnanansari]

the average formula is to divide the sales by number of working days during the month for that store. (in your suggestions; if there is no sales on some days; the avg will show higher figures which is not correct).