Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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!
Solved! Go to 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
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
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:
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
Works perfectly!!! Thank you so much Dale!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |