Question

How to calculate Yearly Average when few month data are missing

Answer 1

It depends whether you want to skip those values and perform yearly average or assign any default values to those missing values.

Drop the year in column field and then perform either of the below options.

To find the yearly average skipping the missing values 

IF ZN([Sales]) != 0 THEN [Sales] END

Then Drop the field and convert the aggregation to AVG while showing data.

To add missing values to some default and then perform aggregation.

Duplicate the field (sales/ profit/ any measure) and edit the duplicated field name and code.

IF ZN([Sales]) = 0 THEN [CONSTANT VALUE] ELSE [Sales] END.

Here constant value could be average of the same field or any non-zero value.

Then drop the field and calculate the average aggregation

Comments

I have to add missing data months also to calculate average dynamically . My calculations skips the counting the months when data is null or zero.

Here are the two super store sales data tables for question scenario

Table 1: Calculate Avg for each category  by adding all sales and divide by 8 .( total months 8) 

	Order Date	Order Date	Order Date	Order Date	Order Date	Order Date	Order Date	Order Date
	2014	2014	2014	2014	2014	2014	2014	2014
Category	January	February	March	April	May	June	July	August
Furniture	5,964		10,597		17,267	16,903	13,882	14,909
Office Supplies	21,704	7,390	14,317	14,922	14,138	15,297	10,699	29,973
Technology	17,035	6,027		16,138		16,060	23,848	16,634

Avg results:

Furniture: 9940.25

Off Sup: 16055

Tech: 11967.75

*************************************************************************************************************

Table 2: Calculate Avg for each category  by adding all sales and divide by 6( total months 6)

	Order Date	Order Date	Order Date	Order Date	Order Date	Order Date
	2014	2014	2014	2014	2014	2014
Category	January	February	March	April	May	June
Furniture	5,964		10,597		17,267	16,903
Office Supplies	21,704	7,390	14,317	14,922	14,138	15,297
Technology	17,035	6,027		16,138		16,060

Month count should be calculated dynamiacally  including missing month.thanks!

---

In your first scenario, sum of sales for 8 months for furniture -> 79522.

avg -> 79522/8 -> 9940.25

Correct avg -> 79522/6 -> 13253.66

Which avg do you want to show? avg or Correct avg.

---

Requirement is to get avg not correct avg .

Calculation should be dynamic as the no of months increase in year it should be divided by total no of months. If all months have data then there is no issue with avg calculation.

---

I used sample superstore data to show you an average of monthly sales ( day sales have missing values) [I used Year -> Month, Month -> Day where day sales have missing values.]

The month day sales as below.

Create a calculated field as below to find the number of months.

NO OF DAYS = { fixed MONTH([Order Date]) : COUNTD(DAY([Order Date])) }

Create another calculated field to find the average as below for each category.

DYNAMIC AVERAGE = SUM([Sales])/SUM([NO OF DAYS])

Drop the field in the table to get average.

The below table consists of sum(sales), aggregated avg from sales, No of days, and the dynamic average calculated.

For yearly sales, try substituting MONTH as YEAR and DAY as MONTH.

---

Hey! I hope you are doing good.

Register at Edureka Community and contribute(ask, answer or comment) and earn points and reputations.

Answer 2

Hi, You can try by manually calculating sum of all values by number of values of not missing rows or filter out missing value rows and calculate the average.

You can create a field to check whether value in field is missing and then count if data is not null, then create field to perform average using the count of rows with no missing values.