Average calculation

[ebrownretail]

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.

[Ramya_Shree]

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.

[ebrownretail]

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?

[Ramya_Shree · Accepted solution]

yes,adding a column to your sales history table that lists the snow range for each week can simplify the process.