cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AmberM Regular Visitor
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 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Average per Store
UPCCustomer 100001010101001010
UPCCustomer 2000010101010101010
UPCCustomer 3000010101010101010
UPCCustomer 4000010101010101010
UPCCustomer 5000010101010101010
UPCCustomer 6000  101010101010
UPCCustomer 7000  101010101010
UPCCustomer 8000   1010101010
UPCCustomer 9000   1010101010
UPCCustomer 10000   1010101010
UPCCustomer 11000    10101010
UPCCustomer 12000    10101010
UPCCustomer 13000    10101010
UPCCustomer 14000    10101010
UPCCustomer 15000     101010
Total $ Sales Each Week 00005070100140150150150
Actual Store Count Each Week 0000571014151515
Average $ Sales/Store/Week     10101010101010
             
With Zero Weeks Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Average per Store
UPCCustomer 10000101010100108.333333333
UPCCustomer 2000010101010101010
UPCCustomer 3000010101010101010
UPCCustomer 4000010101010101010
UPCCustomer 5000010101010101010
UPCCustomer 6000  101010101010
UPCCustomer 7000  101010101010
UPCCustomer 8000   1010101010
UPCCustomer 9000   1010101010
UPCCustomer 10000   1010101010
UPCCustomer 11000    10101010
UPCCustomer 12000    10101010
UPCCustomer 13000    10101010
UPCCustomer 14000    10101010
UPCCustomer 15000     101010
Total $ Sales Each Week 00005070100140140150148.3333333
Actual Store Count Each Week 0000571014141514
Average $ Sales/Store/Week     10101010101010

 

Help!

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
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
Highlighted
v-jiascu-msft Super Contributor
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.
AmberM Regular Visitor
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.

 

Link to sample dataset:

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

v-jiascu-msft Super Contributor
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.
AmberM Regular Visitor
Regular Visitor

Re: Measure to calculate true item velocity with nested AVERAGEX

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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 122 members 1,614 guests