3 Replies Latest reply on Aug 29, 2017 3:31 AM by Zhouyi Zhang

# Using number of records from the entire data source

I need to calculate the percentage coverage of some fields. i.e. if you have columns in a spreadsheet which are sometimes null, what percentage of those values are not null? Obviously this is a very simple calculation...

number of records where specified field is not null / total number of records in data source

... however, I'm having trouble doing it in Tableau. So my question is, how can I use a particular measure in a calculation with the total number of records in the data source? Here's what I have so far:

So, I realize that using SUM(Number of records) is not correct because it will only give me the count of records where the J1 field exists. How can I change it so that it counts all rows in the source, regardless of the existence of the J1 field?

• ###### 1. Re: Using number of records from the entire data source

Hi, David

If you want to retrieve total number of records in the data source, you can use

{ FIXED :SUM([Number of Records])}

which will give you the total number of rows in data source.

To you calculation it will be

sum(if not isnull([J1]) then [Number of Records] end ) / sum( { FIXED :SUM([Number of Records])})

Hope this could help, if it doesn't work, it is better to share a sample workbook with your issue as different data may result in different solution

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Using number of records from the entire data source

Hi Zhouyi,

Brilliant, thank you very much. I needed the fixed number of records by date, so I used {FIXED [Date]: SUM([Number of Records])}.

I also found some of you old notes here ("Fixed" Function Behaviour ) explaining how the FIXED function can be used similar to a group by/partition in SQL. I found this extremely helpful as well. Thanks again.

• ###### 3. Re: Using number of records from the entire data source

Hi, David