Forum Discussion
Average calculation
Hi!
I have something i am trying to build but not really sure how to start it. Below are the parameters:
- Average historical Sales
- Only average the sales in that week if the forecasted snow range = the historical weeks snow range
Example:
Store 5 is getting 3 inches of snow
- snow range is 1-3 inches
- find all the week #'s that had 1-3
- Average the sales of all the week #'s in the above find
It would avg the Total Sales in these specific rows only with the above paramenters, not the sales in any others:
I have a dates table with the year&week # called 'Dates'
I have a measure that finds the forecasted snow range
I have a measure that finds the historical snow range
I have a Sales History table that has the year&week listed
How do I build a formula that takes the forecasted snow range measure, finds the year&week #'s that meet that range from another measure, takes those week numbers and finds the sales in the sales table, then averages those sales.
yes,adding a column to your sales history table that lists the snow range for each week can simplify the process.
3 Replies
To calculate the average historical sales based on the forecasted snow range
Filter Sales Data:
- Create a measure to filter the sales based on the forecasted snow range.
- SalesInRange =
VAR ForecastRange = [ForecastedSnowRange]
RETURN
CALCULATE(
SUM('Sales History'[Total Sales]),
FILTER(
'Sales History',
[HistoricalSnowRange] = ForecastRange
)
) Calculate Average Sales:
- Create a measure to calculate the average sales for the filtered weeks.
- AverageSalesInRange =
VAR TotalSales = [SalesInRange]
VAR WeekCount =
CALCULATE(
DISTINCTCOUNT('Sales History'[Year & Week]),
FILTER(
'Sales History',
[HistoricalSnowRange] = [ForecastedSnowRange]
)
)
RETURN
DIVIDE(TotalSales, WeekCount) - This approach filters sales by the forecasted snow range and then calculates the average sales for those weeks.
hi!
I just tried this, but it didnt come back with anything. Do i need to add a column to my sales history that lists what the snow range was for that week?
yes,adding a column to your sales history table that lists the snow range for each week can simplify the process.
