This might be a difficult question to answer, since I have tried recreating the issue in a test file with less data, but without success.
The issue I've been having is, I have a large view containing about a billion financial transaction records. Each transaction has a unique identifier--a fact key. I have limited my view to about 12 snapshots. I do a CNTD of the Fact key to arrive at the number of unique transactions occurring in each snapshot for each branch of my institution. I add a Grand Total onto each row to show the total transactions over the time period of the dataset, but the Grand Total doesn't match up with the sum of the individual snapshot totals. How can this be? I have attached a screenshot of my workbook, but I am unable to publish what I have, and as mentioned, when I created a dummy test file, everything seemed to work just fine.
The problem is, although the grand total tally is insignificant in some cases, in others, it's off by 2%. I have looked at other dashboards I have created, and they are off by even more--something like 50%. I suspect the issue is related to the fact that Tableau is deficient when it comes to aggregating aggregations (doing a sum of unique counts). Is there a way to make this feature work correctly?
Screenshot attached.
Hi Deepak. I dived into my data deeper, and found that some transaction keys were being duplicated in the DW across separate snapshots. Whether or not our ETL developers intended this, I do not know, but I'll need to find out. I don;t believe it should be the case. However, realizing this, I instead did a simple count of the fact keys, and am now arriving at the correct number with Grand Total. My frustration with this issue probably stems from my understanding of how Tableau has programmed the Grand Total to function. In my mind, it should function as a sum across the row values. In reality, it does not. It functions as though snapshot date is being ignored, hence the reason why when I had the function as COUNTD, I was getting lower numbers across the 12-month period because of the dupes. Thanks for your time with this, it's much appreciated.
