In some cases you can mess around with the grand total value.
Consider this calc:
IF FIRST() <> LAST() the SUM([Value])
[use a calc that computes what you want]
The values of FIRST() and LAST() are equal for the grand total row. So for all other rows, display the normal SUM(), and for that last one, do something different. (And then use that calc instead of SUM([Value]) that you're currently using.)
But this doesn't always work -- especially if the field that is getting displayed in the data rows is an aggregate calc or a table calc.
For kicks, create that calc above, but just display 9999 for now and see if you get 9999 in the grand total row. If so, then we can figure out what calc will give you the distinct sum you are looking for.
Hi Joe Oppelt
Thank you for your reply,
i am able to follow as you mentioned.
1) The sum is aggregated calculation.
2) i tried to keep 9999 value for your calculation but some how i didnt get grand total as you mentioned.
Can you please check once.
For starters, I have the 999 working for you. (I missed that you are doing grand totals both across and down. I'm assuming you want the altered value to be the total running down.)
So we have that, and it's going to work for you.
Next, to figure out how to eliminate the dup Product rows, tell me about how/when this would occur. Could PRD9002 also show up in a different [Industry Name1] ? (We have only one on this sheet right now.) And if it could show up under a different Industry, would it fall under the same [Brand1] names? (So, for example, could it show up for Company1 AND Company2? And if so, would it always be either Brand7 or Brand7A?)
If it shows up under different companies, are you looking to eliminate only duplicates within each company?
Community Test1_v10.5.twbx 20.7 KB
Hi Joe Oppelt
Thank you very much for your reply,
Yes Product 9002 can show up in different industries, yes this product fall under the same brand Name.
(So, for example, could it show up for Company1 AND Company2? And if so, would it always be either Brand7 or Brand7A?) ---- YES
If it shows up under different companies, are you looking to eliminate only duplicates within each company? --YES
Sheet 1 in the attached uses mu Calculation2 to shove 999 into the totals. That's what I uploaded before.
Sheet 1(2) -- I added SUM(Sales) to text so that you can see the sum you were getting before the 999s were added in. So now in the grand total has 2 values in each cell: My 999 for the "total" from calculation2, and 296,423 (in the first column) for the Tableau-generated total.
Sheet 1(3) -- Now I'm using Calculation3. it's the same as Calc2, except I have my own calc to get the correct total. I now have 3 fields per mark: Calc3 (which is the sum(sales) in the data rows), sum(sales) (which should match Calc3 in each data row cell), and [Skip Duplicates]. Take a look at that calc. Actually, first look at [First occurrence of Duplicate] in the dimensions. This calc identifies only one [Brand] per [Product]. (I think I could have left off the dimension for the month of Date. But it doesn't change anything so I just left it in.) I used MAX to pick one [Brand], but MIN would have worked just as well. (It just would have identified BRAND7 instead of BRAND7A.)
In the [Skip Duplicates] calc I tell Tableau to load the value only for the one identified [Brand]. Other rows will be empty. And you can see that for PRD9002, I only get a value on BRAND7A.
I use that calc to build a grand total. You'll see in Calc3 I grab the SUM(skip Duplicates) instead of SUM(sales) when FIRST=LAST. And you can see that in place of 999 now, I show 292,764. Below it is the grand total of SUM(sales) (296,423). The third value in the grand totals is the tableau-generated SUM(skip duplicates). If you were to drag that off the TEXT shelf, my calc still results in the right amount. And you get the correct display of SUM(sales) for both those data rows for Brand 7 and Brand 7A.
Community Test1_v10.5 A.twbx 48.0 KB
Thank you very much for your help, Thank you again for spending time for this.