Go to Analysis, Uncheck the Tick for Aggregates
Unfortunately when I do that I don't get 5026 records - in exchange I get a series of 1,1,1,1,1,1,1,1,1,etc.
Looks Like it is Skipping Nulls..Try COUNTD(ZN(Rec ID))
All the aggregate functions MIN(), MAX(), SUM(), AVG(), COUNTD() etc ignore null records by design. Typical SQL behavior.
So AVG([Salary]) really means the average value of the Salary field for all records that have a non-null value in the Salary field. COUNTD() behaves similarly.
That is much better than having an error thrown for nulls, which would make you complicate your calculated fields with null checks like many Java programs are written. But it means that as an analyst, you have to be aware that aggregation functions silently ignore nulls.
Along these lines, the purpose of COUNT([Some Field]) is to return the number of records that have a non-null value for [Some Field]. COUNT() does NOT behave like SUM() .
So one of the first things to do when diagnosing a situation like this where you suspect null values may play a role, is to compute COUNT([Rec ID]) to see how many records you have that have some value in [Rec ID]. You can compare that with SUM(1) or (SUM([Number of Records]) if you like typing) to see if if you have any records with null [Rec ID]. Or alternatively, try MAX(ISNULL([Rec ID])) to test whether there are any records with a null [Rec ID]
After all this, its possible that null values for [Rec ID] is not your issue, but computing these values are one step towards diagnosing your issue.
Hi Andy, I don't think Excel Pivot Tables can count distinct values. Maybe you have a cool later version of Excel that does, but in my version I an only count records.
So in Excel Pivot Table, this will count 6 records. In Tableau, if we do COUNTD([Value]) this will give us 4. I think if you do COUNT([Value]) in Tableau you should also get 6.
If you want to verify the unique values count in Excel, I'd do this:
Copy the list to a new location. Select the column (or multiple columns if is more). Go to the Data tab and hit Remove Duplicates:
You get the dialog box. Make the appropriate selections and hit Ok.
You'll get this dialog box telling you the results, and your duplicates will have been removed:
If you missed what the dialog box said, I would then count the remaining values.
Select the column or list, and then check the status at the Bottom of the Excel window, it will show the Count of non-blank cells selected. (Just subtract 1 for the column header).
Or do a count function, subtracting 1 for the column header.
Hope I understood your issue properly and that this helps.
Use Count instead of Countd.