Daily average calculation issues

[avantime]

Hi all,

I need to calculate the daily average sales for each customer.

Right now I use this formula:

Sum(Sales)/sum({1<Year = $::Year , Month=$::Month, Day=$::Day>} TOTAL <Year,Month,Day> [Working Day Marker])

I load the working day marker from a calendar I made, containing all days in a year, the marker being 0 for weekends or legal holidays and 1 for everything else.

The formula works great if no filters are applied for Year or Month or Day, otherwise it gives the wrong results.. 

Anyone got a solution for this?

Basically what I want to do is to split customer revenue to the total number of working days in selected period.

Thanks!

[gn_marvs1989]

Have you tried to use Aggr() instead of TOTAL? 

If you are calculating per dimension on one of your Sheet objects or Charts, you might need to use Aggr()

e.q. Avg Sales per Customer  in a straight table.

[avantime]

I don`t need Avg. 

I need to divide the clients sales to the number of working days in the period selected (Years, Months, Days), not to the number of days he made purchases in.

So, by example, let`s say the client made 3 purchases in 3 different days of Jan 2019, if I filter the date to Month Jan and Year 2019, I need to split the clients sales to 20 working days in my country for January.

Hope I made myself understood.

Thanks!

[avantime]

Up! Any other ideas?

[avantime]

I`m also attaching a test qvw, it`s easier to understand what i want.