Forum Discussion
Distinct Count is one number off?
Solution SageDo the numbers match if you use the DISTINCTCOUNTNOBLANK() function instead? If you have values that exist on the many side but not on the 1 side of the relationship, a blank row is created.
Pat
DISTINCTCOUNTNOBLANK() returns a number different than both of the other methods. lol. Distinctcountnoblank is about 5 less than Column B on the same day. It's also returning different results than either of the other two columns for the other days whereas the numbers of A and B are the same. The count from the source data matches the amount in column A.
I thought CountA would skip counting blanks? Also, I'll add the these columns should only be the same under the current filter conditions. For example Column B is calculated using distinct time of submission. So, there are up to 21 rows per submission where 21 entries in a specific column could be blank (due to unpivoting 21 columns). By counting the time, I'm also counting entries where the the said column should be blank for up to 21 blanks. There are instances where all 21 of said unpivoted columns (hence 21 blanks are in place of 1) would be accurate, but there is other data that is needed from the rows in that situation.
For Column A, the count is straight forward. I just need to count the entries that are not blank hence the use of countA (As it appeared it would skip counting blanks).
With 2 specific filters, the scenario should always return the same result with both of my calculations, or so I thought. If you total all of the As and compare to the Bs, they add up to the same number, but the total for column B that is auto calculated does not.