Forum Discussion
Solved
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 ...
yes,adding a column to your sales history table that lists the snow range for each week can simplify the process.
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.
