11 Replies Latest reply on Sep 24, 2018 3:29 PM by Boreak Silk

# Average with NULL

Hi Tableau Users,

I'm trying to compare sales for the past 12 months to average sales by month and category. I came up with below formula

to calculate average:

{ FIXED DATEPART('month',[Date]), [Category] :

AVG({ FIXED DATEPART('year',[Date]), DATEPART('month',[Date]), [Category]:

SUM([Value])})}

it does not work correctly because it does not count months when no sales (NULL), which should be zero in my analysis.

Any advice how to fix this? Attached is my workbook.

Bo

• ###### 1. Re: Average with NULL

Hi Boreak,

If you ZN the sum does this work for you ?

HTH

Peter

• ###### 2. Re: Average with NULL

Hi Peter,

Tested and it did not work as expected.

Thank

• ###### 3. Re: Average with NULL

Hi Boreak,

Not sure what number you are looking for on your graph. Is it something like this?

~Tushar

• ###### 4. Re: Average with NULL

{ FIXED DATEPART('month',[Date]), [Category] :

SUM([Value])})}/{countd(month(date)}

• ###### 5. Re: Average with NULL

Thank Tushar,

I'm looking for average by month and by category, something similar to the graph below where bars are

sales for the past 12 months and line are averages by month and category over the entire period, Jan 2010 - Aug 2018.

My formula adds up all the sales and divides it by number of months with sales, it does not count the months

where there is no sales. This is incorrect and I wanted to fine a solution to work out average over the total number

of months, with and without sales, during the entire period.

Hope this explains.

• ###### 6. Re: Average with NULL

Thank Deepak.

Can you please elaborate your formula? It is not working in the current form.

Did you mean

{FIXED DATEPART('month',[Date]), [Category] : SUM([Value])}/{countd(month([Date]))}

If yes, it does not give answers as expected.

The correct numbers should be as those in green below but my formula gets those in red.

Those highlighted in yellow are incorrect averages where there are some months that have no sales.

Any further help will be appreciated.

Regards

• ###### 7. Re: Average with NULL

Try This:

SUM(({FIXED DATETRUNC('month', [Date]),[Category]:SUM(Value)}))/SUM(({COUNTD(DATETRUNC('month', [Date]))})+1)

Thanks

Deepak

• ###### 8. Re: Average with NULL

Hi Deepak,

The correct answers should be as numbers in green above.

Thank.

Bo

• ###### 9. Re: Average with NULL

Sorry, Can you Tell how are you getting those Values? You may attach your Excel file.

• ###### 10. Re: Average with NULL

Hi Deepak,

Here are the tables that I created in the same Tableau file that I posted, in Dashboard 1.

Below is its screenshot and as shown, I wanted to get nine Jan-Aug and eight Sep-Dec so that I can get the correct averages.

• ###### 11. Re: Average with NULL

Hi All,

Found a solution - use Tableau Prep to prepare a master table to add 0 to those months without sales.

For further information, refer to this blog by Kathryn Bridges at Interworks, https://interworks.com/blog/kbridges/2018/09/17/a-use-case-for-tableau-prep-using-a-master-table-to-see-all-records/

Cheers

Bo