I am trying to nest AVERAGEX expressions to calculate true item velocity at an item level.
Average $ Sales per Store per Week with zero weeks - Accurate measure of an item's $ sales rate in terms of dollars per store per week. Includes zero POS weeks in query after first POS record for each store.
Average $ Sales per Store per Week without zero weeks - Accurate measure of an item's $ sales rate in terms of dollars per store per week. Does not include zero POS weeks in query after first POS record for each store.
Parameters for both calculations:
Both types of measures do not begin counting a week in a store until the first week in the query that the store sells the item.
Without zero measures give you the option to exclude zero sales weeks in your query that occur after a store’s first record of POS for that item.
If the data is pulled at a higher level than upc, such as brand, it will represent the AVERAGE VELOCITY for the items in the selection.
for example, the items in a brand may have Units per Store per Week (w/o zeros) that range from 18.1 to 26.8. If you choose to eliminate item columns and pull back at the fineline level, you may get a number like get 24.2. Therefore, the average velocity of items in that fineline is 24.2
My expression is not calculating through occurences as written:
Average Sales per Store per Week =
VALUES ( Dates[FiscalWeek] ),
I have 2 example data sets below, 1 with zero week, 1 without zero weeks
Thank you for ofering to help!! I have the upc table and customer table separate in my model, but for the sample data set, I included those 2 columns in each table. Please let me know if I need to split apart!
The real data is at a daily level, but it also includes a fiscal week number which is what I am trying to roll up to.