Jul 14, 2019 in Tableau 184 views

## 2 answers to this question.

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
• 32,440 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

## 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

## How to calculate weekly average from daily data?

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

## How to calculate Session and Session duration in Firebase Analytics raw data?

First you need to define a session ...READ MORE

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

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

## How to use tableau to searh for text within entire data

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

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

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

## How to determine average per event by year?

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

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

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