Forum Discussion
Average Weekly Sales
GOT IT!!!
AWUS Actual =
VAR __CATEGORY_VALUES = VALUES('Keys'[MonDate])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(SUM('Keys'[Royalties]) / DISTINCTCOUNT('Keys'[Store Name]))
),
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(DISTINCTCOUNT('Keys'[MonDate]))
)
)
DaveyP,
I have something that may work for you as it is something similar to a project of mine.
With the data you supplied:
Create two measures:
TotalSales = SUM(Table1[Sales])
CountWeeks = DISTINCTCOUNT(Table1[Week])
The third measure for Avg/Week:
AvgSales/Week = DIVIDE([TotalSales],[CountWeeks],BLANK())
To get something like the following:
This should work with your dataset.
In my project, while I have a dCalendar table, I had to take a DISTINCTCOUNT of the 'Dates' from the working table rather than the dCalendar table so the denominator was in the 1000's range rather than10,000's.
Hi Dozer
Thanks for your reply but this still doesnt give the right result, it is still total sales/total distinct weeks and doesnt work when 1 store wasnt open for that week
Dave
Hi DaveyP, may I ask what you expect the answer to be in your dataset that you provided?
Hi Dozer
Sorry to confuse, below is the example in more detail, the results i keep getting are essentially all stores sales added together (9300) divided by count of stores (2325) divided by count of weeks (581.25)
Week Store1 Store2 Store3 Store4 AWUS 1 400 500 700 600 550 (All stores sales divide by count of store (4)) 2 500 600 800 700 650 (All stores sales divide by count of store (4)) 3 600 700 900 800 750 (All stores sales divide by count of store (4)) 4 700 800 750 (All stores sales divide by count of store (2)) 675 Average sales = all weeks AWUS / Count of weeks
Well this is interesting...adding an Average Line, You get the $675 but I do not know how it's calculated.