1 of 1 people found this helpful
The [Amount Monthly] grand total is obeying the calc. That's what it's going to do.
Thanks Joe. Sorry I wasn't clear with my question. I recognized that the Grand Total is following the calculation logic. I guess I should have asked, how can I get the correct numbers in the Grand Total line. Thoughts?
I started working on that right after I posted my previous reply.
You have to make your own totals.
Sheet 5 is a dup of sheet 4. Then I added two new table calcs: [Total Amount] and [Total Amount Monthly]. I changed them to do Specific Dimensions, and to restart every month. Ignore what shows in the "Grand Total" chunk of the viz. You will see the value showing up for each [Table], and they have the values you need.
Sheet 6 is a dup of sheet 5. Then I moved the two old measures to the detail shelf. And I killed the TOTALS from "Analysis". All that leaves are the two Total calcs displayed.
Sheet 7 is a dup of sheet 6. Then I moved [Table Name] to the detail shelf. (We still need it on the sheet to run through the table properly.) Now you see 4 values of each Total for each month. That's how Tableau works.
Sheet 8 is a dup of Sheet 7. I created a calc called index. I put it in the filter shelf and changed the table calc to Specific Dimensions and restart every month. Now it's "walking" the table the same way the Total calcs are. I edited the filter just to get value = 1. Only get one index from each cell.
End result is a new sheet that gets your totals for you.
Sheet 4A is a dup of Sheet 4 without the Totals from "Analysis".
Dashboard 1 positions the two sheets so that make one viz for the user.
Special note: You will notice on the interim sheets that Tableau is labeling [Total Amount] as "Total Amount along Month of ..." Tableau does that. Now, I'm not totally familiar with 10.0 yet, but I see that it's not doing it to [Total Amount Monthly]. Not sure why. In this TWBX we could ALIAS that correctly, but if, in your actual workbook, you are using a data extract, I think we still cannot do ALIAS on fields from TDEs. So in the dashboard I hacked up a mask of a blank container that I colored white and floated it over the extraneous words, and that's why you don't see "along ..." displayed there. (That's just window dressing.)
Anyway, when it comes to "Grand Totals" of calcs, it can get a bit messy, and I just make my own the way I want them and artistically display it on a dashboard like this.
previous value not summing A.twbx 100.9 KB
Of course, you don't need all those interim sheets. I just left a trail of sheets for a step-by-step demonstration of that I did in there.
This is amazing. What an awesome detailed response. Thank you very much.