How to calculate Yearly Average when few month data are missing

+2 votes
Jul 14, 2019 in Tableau by Rekha
• 170 points
7,774 views

2 answers to this question.

+2 votes

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, 2019 by anonymous
• 33,030 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.

Hey! I hope you are doing good.

Register at Edureka Community and contribute(ask, answer or comment) and earn points and reputations.
+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, 2019 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 ffdfd
• 5,550 points
1,218 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 27, 2018 in Tableau by Atul
• 10,240 points
14,032 views
0 votes
1 answer
0 votes
1 answer

How to replace the count(field) which is missing in data with zero in tableau?

Use calculated field to create a dummy ...READ MORE

answered Dec 21, 2019 in Tableau by sindhu
2,039 views
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,560 points
841 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,560 points
891 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
882 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, 2019 in Tableau by Cherukuri
• 33,030 points
927 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, 2019 in Tableau by Cherukuri
• 33,030 points
839 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP