11 Replies Latest reply on Aug 23, 2016 3:00 PM by Beth Hurst

# How to change the order of operation in a Grand Total of a Calculated Field

I have a calculated field called 'Score Current'.  I'm having Tableau compute using 'Vendor' and am displaying this score by 'Term Cd'.  The answer is what I expected it to be for the individual 'Term Cd' by Vendor.  However, the Grand Totals are not behaving the way I want them to.  I want Tableau to Sum the Products, but it is Summing first then calculating the resulting product.  I've attached a workbook.  I'm using Vendor A as an example in the Excel screen shot below.  Tableau returns a Grand Total for 'Score Hist' of 34.3 instead of 26.4.

• ###### 1. Re: How to change the order of operation in a Grand Total of a Calculated Field

Let me know if this helps or not!  Tableau is a very different mental model from Excel, and so aggregation is always important to account for.  If this doesn't resolve the issue, can you provide some expected values or a mockup of expected results so I can apply help to your workbook and ensure it is what you are after?  Thanks and best of luck!

1 of 1 people found this helpful
• ###### 2. Re: How to change the order of operation in a Grand Total of a Calculated Field

I watched the video, and had previously read and tried many hours’ worth of different things to try to get the right answer, but I still don’t know how to do it.  I had posted the expected value of the Grand Total of the ‘Score Hist’ that I expected for Vendor A = 26.4 (1.8 + 4.5 + 4.9 + 7.2 + 8 = 26.4).  Tableau calculates 34.3.  Mock up is in the Excel picture I pasted in my post.

Can you try to get the expected answer using my sample workbook?

• ###### 3. Re: How to change the order of operation in a Grand Total of a Calculated Field

Sorry, I had not looked at your workbook.  It appears that you are working with a nested table calculation here, and that can be a bit tricky.  I do not know the immediate solution, but am going to ping Jonathan Drummey in case he has some spare time to take a look.  I'm not 100% clear on the data or the expected results, but I have a feeling he may be able to provide some tips to help guide you here.

Here are some links that discuss the topic a bit:

• ###### 4. Re: How to change the order of operation in a Grand Total of a Calculated Field

I took a quick look at this and given that there are ~8 levels of nested table calculations here and adding a duplicate dimension to the LOD will break some/most of the existing calculations that having a much simpler start would be better, can you mock up one set of calculations (like the set of CPE calcs)?

One direction that might ultimately be easier is to use INCLUDE/EXCLUDE LOD expressions than try to do this with table calculations.

Jonathan

3 of 3 people found this helpful
• ###### 5. Re: How to change the order of operation in a Grand Total of a Calculated Field

Hi Jonathan,

Following easy direction, here in the attached is a LOD variation of the above.

The last step (calculating the anticipated Grand Total for the [YF : Score Hist])

requires the INCLUDE LOD expression, hence replacing blended [Value] field

with in-Primary row-level one.

Yours,

Yuri

if there would be a simple way

to do a seemingly complicated thing.

1 of 1 people found this helpful
• ###### 6. Re: How to change the order of operation in a Grand Total of a Calculated Field

Jonathan and Yuri,

Thank you for helping me out with this.  I haven't dug into Yuri's file yet, but did see that the Grand Total is what I expected.  Many thanks!!

• ###### 7. Re: How to change the order of operation in a Grand Total of a Calculated Field

Thanks, Yuri!

FYI the reason why this works is that INCLUDE/EXCLUDE LODs have a special property with regards to subtotals and grand totals where the dimensionality of the LOD expression based on the vizLOD and INCLUDE/EXCLUDE dimension declarations is used to compute the row-level LOD result in both the detail rows *and* the subtotals and grand total rows, then that is aggregated to the vizLOD in the detail rows and the appropriate grain in the subtotals and grand total rows.

Of course, this would have "just worked" if Tableau supported options other than Automatic for grand totals and subtotals, vote up https://community.tableau.com/ideas/1232 if you'd like this added to the product.

Jonathan

1 of 1 people found this helpful
• ###### 8. Re: How to change the order of operation in a Grand Total of a Calculated Field

Yippee!!!  Thank you for the education as well as helping me with my immediate issue of getting the totals to work.  You guys (on the forum) are awesome!!

• ###### 9. Re: How to change the order of operation in a Grand Total of a Calculated Field

I'm wondering if you might be able to look at this next issue I have with the same workbook.  For Vendor A, I'm attempting to take the 'Score Current' for Term Cd '1168' (30.7) and add to it the 'Score Hist' Grand Total (I modified the formula slightly to weight this score; 5.3) to get a result of 36.0 for 'Score Combined' Grand Total.

I did attempt to do several different things prior to reaching out to you again, but none of the calcs I tried resulted in a Grand Total for Score Combined of 36.0.

• ###### 10. Re: How to change the order of operation in a Grand Total of a Calculated Field

Hi Beth,

There is no value for '1168' in [YF : Value] CASE calc.

You may put a WHEN '1168' THEN 0 there (before the END)

to get the same [Score Hist] Total, then add TOTAL([Score Hist])

part to your [Score Combined] (it becomes a table calculation then).

Hope it helps (though I have no idea what is it for).

Yours,

Yuri

1 of 1 people found this helpful
• ###### 11. Re: How to change the order of operation in a Grand Total of a Calculated Field

Yuri, that's exactly what I was looking for.  FYI, it's a method of scoring our marketing vendors so that we can make more informed decisions about spend.

Thanks again!