cancel
Showing results for
Did you mean:
Regular Visitor

## Measure to calculate true item velocity with nested AVERAGEX

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Measure to calculate true item velocity with nested AVERAGEX

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super Contributor

## Re: Measure to calculate true item velocity with nested AVERAGEX

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Measure to calculate true item velocity with nested AVERAGEX

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.

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

Super Contributor

## Re: Measure to calculate true item velocity with nested AVERAGEX

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

## Re: Measure to calculate true item velocity with nested AVERAGEX

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

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 44 members 1,106 guests
Recent signins: