7 Replies Latest reply on May 21, 2018 5:44 AM by Ruben Arias

# Percentage of Total

Hello Tableau'ers.  I have a sheet with a calculated field which takes the \$ diff and divides it by the total paid dollars.  This gives me the % diff, etc.

Here is the calc field for \$ diff :

[Calc : Total Billed \$]-SUM([Calc : Total Paid])

Now, the last column called % diff is the following calc:

[Calc : Total Billed \$] / SUM([Calc : Total Paid]) -1

My issue is, in the totals/sub totals % cell, it is calculating either SUM, AVERAGE, AUTO, MIN, MAX, etc...but the % is not correct for the totals/sub totals.  It is correct across the above rows.

So if you look at the screenshot, right now I have it set to AVERAGE for the % totals.  It says %34.34, but really I want it to read the actual % which is %29.62 (\$175.50/\$592.50=.29620253)

How do I accomplish this simple task.

Thanks, Ruben

• ###### 1. Re: Percentage of Total

Hi Ruben

Could you please attach sample data as twbx format?

Thanks,

Shin

• ###### 2. Re: Percentage of Total

Sorry, forgot to extract data, see attached...ty!!

• ###### 3. Re: Percentage of Total

Good morning

See the attached

this formula is a table calculation

is this what you expected?

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Percentage of Total

Hi Jim, I see the % is 58.55, when it should be 29.62 for the first total line.

I am taking \$175.50/592.50 and getting 29.62.  Thats what all the totals should calculate by, the total \$ diff div by the total paid dollars...

• ###### 5. Re: Percentage of Total

Ruben

First of all, Tableau is not good at all to create Table format view like Excel.

Based on my experience, this is "do-able" with 1000 hours efforts maybe, means not realistic to chase by Tableau.

When you have Conditional sum, sub total or G.Total logic becomes different because of the layer of aggregation.

To make sub/G.total correct, I usually use technique of duplicating data with Union PROVIDED number of measure value is a few.

Once duplicate the data, all other measure values needs to be re-build.

Re-build does not mean "modify".  need to add another set of all the measure value.

Seeing your data, it's not realistic...

I will not analyze all of your calculation formulas but you may try using "Tableau prep"..

Thanks,

Shin

.

• ###### 6. Re: Percentage of Total

Hi Ruben,

The problem is most likely due to [Total Billed \$] being pre-aggregated. Table calcs could work but it may be fiddly. LOD is the next best approach.

There are many filters in the worksheet. So, I have added all the dimensions to the LODs. This will allow the calculations to respond to each filter. Your data is very granular. So, calculating the LOD expression at similar level of granularity is essential. See attached workbook.

Hope this helps.

Ossai

• ###### 7. Re: Percentage of Total

Thank you Ossai, that did the trick, and I have marked it as correct answer.  One more question since I have your attention.  There is a measure named sage invoice which I previously had after %diff but now I moved it to a dimension after WO#.  The reason being is it was repeating the sage invoice amount on every line, but I just want to display it as a subtotal for every WO# only.

Is there a way to hide the detail for every line but just display the subtotal?

I attached a your workbook for you to see what I mean.

Thanks, Ruben