Measure to calculate true item velocity with nested AVERAGEX

[AmberM]

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 = 
AVERAGEX (
 VALUES ( Dates[FiscalWeek] ),
 AVERAGEX( 
  VALUES(Regions[CustomerName] ),
[Total Sales]))

I have 2 example data sets below, 1 with zero week, 1 without zero weeks

Without Zero Weeks		Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Average per Store
UPC	Customer 1	0	0	0	0	10	10	10	10	0	10	10
UPC	Customer 2	0	0	0	0	10	10	10	10	10	10	10
UPC	Customer 3	0	0	0	0	10	10	10	10	10	10	10
UPC	Customer 4	0	0	0	0	10	10	10	10	10	10	10
UPC	Customer 5	0	0	0	0	10	10	10	10	10	10	10
UPC	Customer 6	0	0	0			10	10	10	10	10	10
UPC	Customer 7	0	0	0			10	10	10	10	10	10
UPC	Customer 8	0	0	0				10	10	10	10	10
UPC	Customer 9	0	0	0				10	10	10	10	10
UPC	Customer 10	0	0	0				10	10	10	10	10
UPC	Customer 11	0	0	0					10	10	10	10
UPC	Customer 12	0	0	0					10	10	10	10
UPC	Customer 13	0	0	0					10	10	10	10
UPC	Customer 14	0	0	0					10	10	10	10
UPC	Customer 15	0	0	0						10	10	10
Total $ Sales Each Week		0	0	0	0	50	70	100	140	150	150	150
Actual Store Count Each Week		0	0	0	0	5	7	10	14	15	15	15
Average $ Sales/Store/Week						10	10	10	10	10	10	10
With Zero Weeks		Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Average per Store
UPC	Customer 1	0	0	0	0	10	10	10	10	0	10	8.333333333
UPC	Customer 2	0	0	0	0	10	10	10	10	10	10	10
UPC	Customer 3	0	0	0	0	10	10	10	10	10	10	10
UPC	Customer 4	0	0	0	0	10	10	10	10	10	10	10
UPC	Customer 5	0	0	0	0	10	10	10	10	10	10	10
UPC	Customer 6	0	0	0			10	10	10	10	10	10
UPC	Customer 7	0	0	0			10	10	10	10	10	10
UPC	Customer 8	0	0	0				10	10	10	10	10
UPC	Customer 9	0	0	0				10	10	10	10	10
UPC	Customer 10	0	0	0				10	10	10	10	10
UPC	Customer 11	0	0	0					10	10	10	10
UPC	Customer 12	0	0	0					10	10	10	10
UPC	Customer 13	0	0	0					10	10	10	10
UPC	Customer 14	0	0	0					10	10	10	10
UPC	Customer 15	0	0	0						10	10	10
Total $ Sales Each Week		0	0	0	0	50	70	100	140	140	150	148.3333333
Actual Store Count Each Week		0	0	0	0	5	7	10	14	14	15	14
Average $ Sales/Store/Week						10	10	10	10	10	10	10

Help!

[v-jiascu-msft]

Hi @AmberM,

Please share the original data. If you can share the pbix file, that would be great. The structure of the data model decides the solution.

Best Regards,

Dale 

[AmberM]

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.

Link to sample dataset:

https://1drv.ms/u/s!AoY5sA-v6cUchDMouuJk69SdE3dy

[AmberM]

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.

[v-jiascu-msft · Accepted solution]

Hi @AmberM,

 Try two measures like this. You can check them out in this file.

withoutzeros =
CALCULATE (
    AVERAGE ( 'without zeros'[$ Sales] ),
    FILTER ( 'without zeros', 'without zeros'[$ Sales] <> 0 )
)

WithZeros =
AVERAGE ( 'with zeros'[$ Sales] )

Best Regards,

Dale

[AmberM]

Works perfectly!!! Thank you so much Dale!!!