How to calculate Yearly Average when few month data are missing ?

+2 votes
Jul 14 in Tableau by Rekha
• 160 points
84 views

2 answers to this question.

+1 vote

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

answered Jul 15 by anonymous
• 28,360 points

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.

+1 vote
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.
answered Jul 31 by raksha

Related Questions In Tableau

0 votes
1 answer

How to calculate profit when cost and revenue are in same column?

You can create the following calculated fields: Profit: SUM(IIF([account_category] ...READ MORE

answered Apr 10, 2018 in Tableau by Atul
• 5,530 points
82 views
0 votes
1 answer

How to calculate weekly average from daily data?

You can create these calculated fields: Unique Weeks ...READ MORE

answered Jun 26, 2018 in Tableau by Atul
• 10,240 points
235 views
0 votes
1 answer
0 votes
1 answer

How to use tableau to searh for text within entire data

You can use a pivot table in ...READ MORE

answered Apr 4, 2018 in Tableau by xyz
• 1,550 points
60 views
0 votes
1 answer

How to split or divide the grouped data into multiple graphs?

One of a simple approach is to ...READ MORE

answered Apr 17, 2018 in Tableau by xyz
• 1,550 points
41 views
0 votes
1 answer

How to determine average per event by year?

Hi Roshan, Create a new calculated field (use ...READ MORE

answered Sep 25, 2018 in Tableau by Machdata
• 340 points
60 views
0 votes
1 answer

How to calculate the percent of records within a group in tableau?

 You can click the measure SUM(Number of Records) and ...READ MORE

answered May 8, 2018 in Tableau by Atul
• 5,530 points
225 views
+1 vote
1 answer

How to calculate the number of hours worked in Tableau?

Try using this LOD expression {fixed user : ...READ MORE

answered Aug 3, 2018 in Tableau by Atul
• 5,530 points
83 views
0 votes
1 answer

How to show data between few points/dates (measure) ?

Hi Akhil, You can show data values or ...READ MORE

answered Apr 24 in Tableau by Cherukuri
• 28,360 points
31 views
0 votes
1 answer

How to get past N months date from data source?

Hi, this is a one solution to your ...READ MORE

answered Mar 1 in Tableau by Cherukuri
• 28,360 points
69 views