1 2 Previous Next 22 Replies Latest reply on May 22, 2015 9:02 AM by Ari Bibas

# Difficult (I guess..) : show total as a mere calculation of above rows

Hello,

I am struggling against something that looks obviously trivial in theory but that appars to be a nightmare for me.

I have created some complicated calculation fields from multiple sources. I want to run my calculations on a subdimension, but to show the total at the dimension level.

Example : Category A, B, C, Subcategories A1, A2, A3, B1, B2...

I want to run my predictive analysis for A1, A2, and A3. Let's say that the output is respectively 10, 15 and 20. I want to show that at A level, we have a total result of 10+15+20 = 45.

The problem is that using the Analysis tool to insert the Total of Columns will have Tableau to run a new calc at A level, giving me another output (my formulas are not linear...).

I am afraid that I cannot use LOD calculations because I am using multiple sources of data( Am I wrong?).

So is there a way to do the most simple thing of the world, showing a mere addition of figures already calculated??

Ari

• ###### 1. Re: Difficult (I guess..) : show total as a mere calculation of above rows

I would suggest checking out the links under "Totals and Subtotals" section here: Common Questions on the Tableau Forums if you haven't already. An alternative to get the total to add up could also be to create a running sum version of the calculated field and then use a filter to only show the last item in the view as the total.

If this doesn't work for you, please consider attaching a sample workbook (with mocked up data as appropriate) that recreates the issue, and clarify the expected result.

• ###### 2. Re: Difficult (I guess..) : show total as a mere calculation of above rows

Thanks a lot.

In fact, I did not find the way to solve my problem.

I created a very simplified example of what I want. I would like the Grand Total MyCalc value to show the sum of the "MyCalc" values calculated above, and not to be calculated again as if Grand Total was a dimension on which we have to run the MyCalc formula.

As you see, I am totally aware that with LOD, it is easily feasible, but in my case LOD is not an option because I have multiple sources...

I could merge my sources at my SQL Database level but it would happen to be a nightmare for other reasons...

Ari

• ###### 3. Re: Difficult (I guess..) : show total as a mere calculation of above rows

Below is a great resource for learning more about customizing totals in Tableau:

http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1/

Can you provide a visual mockup of your desired end result for the sample TWBX you've provided?

Cheers!

• ###### 4. Re: Difficult (I guess..) : show total as a mere calculation of above rows

Hi, Thanks a lot.

My desire result is to mimic the "Mycal (with LOD)" result, but doing it without LOD because of my multiple sources issue.

Again, all what I want is to get, at the  Grand Total row level, the mere sum of the MyCalc values for each subcategory above. Only the sum! But respecting the fact that some of my subcategories return a value of 0 because of a condition not being met.

• ###### 5. Re: Difficult (I guess..) : show total as a mere calculation of above rows

Can you provide a visual mockup of your desired end-result for the view?  I'm just struggling to understand through words -- a visual will help.

Are you saying you want the Grand Total of 534.6 instead of 532.6?

I'm also not clear on why you would prefer one of these calcs over the other - can you add some more detail to clarify?

• ###### 6. Re: Difficult (I guess..) : show total as a mere calculation of above rows

I have the same issue. Ill try to post a sample workbook soon.

• ###### 7. Re: Difficult (I guess..) : show total as a mere calculation of above rows

Sorry, but did you see my workbook attached in response to Shine ?

Maybe it is not visivle by all ?

Here it is again.

If you have already got it but want something else, please tell me.

Ari

• ###### 8. Re: Difficult (I guess..) : show total as a mere calculation of above rows

If you change the "Total Using" to "Sum" on your AGG(MyCalc) measure, you'll get the same total as your LOD calc: • ###### 9. Re: Difficult (I guess..) : show total as a mere calculation of above rows

I see the workbook, but no mockup of your desired results -- I'm just trying to clarify which calc you want to keep and what total you desire.

See my comment above -- the total using solution is simple, if it works for your scenario.

• ###### 10. Re: Difficult (I guess..) : show total as a mere calculation of above rows

For the avoidance of doubt, here is an example, with the following explanations (don't know if the wokbook has been properly attached before...)

I created a very simplified example of what I want. I would like the Grand Total MyCalc value to show the sum of the "MyCalc" values calculated above, and not to be calculated again as if Grand Total was a dimension on which we have to run the MyCalc formula.

As you see, I am totally aware that with LOD, it is easily feasible, but in my case LOD is not an option because I have multiple sources...

I could merge my sources at my SQL Database level but it would happen to be a nightmare for other reasons...

• ###### 11. Re: Difficult (I guess..) : show total as a mere calculation of above rows

Please see my last comment on the "Total Using" option

• ###### 12. Re: Difficult (I guess..) : show total as a mere calculation of above rows

I see it now, sorry I didn't read far enough

• ###### 13. Re: Difficult (I guess..) : show total as a mere calculation of above rows

Thank you, it does work on my simplified example, but not on my original workbook!

The Total Using Option is greyed, maybe again because of that multiple sources issue ?

• ###### 14. Re: Difficult (I guess..) : show total as a mere calculation of above rows

Very difficult to say without being able to see the TWBX, unfortunately.

1 2 Previous Next