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]))
)
)
Hi DaveyP,
Generally, if we want to filter data based on a Date table. We can
1. Create a relationship on the Date table and source table.
2. Add Filter() inside the expression.
Since you didn't share us your table structure or some sample data. It is hard for us to give a specific solution. Thus, please kindly share us more information which can help us understand your requirement more clearly.
Thanks,
Xi Jin.
Sorry let me try and be more clear, in the example below 4 stores accross 4 weeks. The average store sales for the group is not simply total sales / number of stores / number of weeks. This wont work because 2 stores were not open in week 4 so to work it out i need to work out store by store then average it eg -
Store 1 total sales / 4
store 2 total sales / 4
store 3 total sales / 3
store 4 total sales / 3
Then average the 4 results
Question is how can i do this in DAX in a way that both the stores and weeks are still filterable on the page.
| Store | Week | Sales |
| Store 1 | week 1 | 400 |
| Store 2 | week 1 | 500 |
| Store 3 | week 1 | 600 |
| Store 4 | week 1 | 700 |
| Store 1 | week 2 | 500 |
| Store 2 | week 2 | 600 |
| Store 3 | week 2 | 700 |
| Store 4 | week 2 | 800 |
| Store 1 | week 3 | 700 |
| Store 2 | week 3 | 800 |
| Store 3 | week 3 | 900 |
| Store 1 | week 4 | 600 |
| Store 2 | week 4 | 700 |
| Store 3 | week 4 | 800 |
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?
