12 Replies Latest reply on Aug 21, 2018 10:20 AM by Ken Flerlage

# Percentage of Grand Total

Hello Tableau Experts!!!

I'm in need of a method to calculate total percentage.  Across each row the calculations are fine (Productive Hours/Eligible Hours), but I cannot seem to get the grand total to do the same.  What I want is 164.83/575.08.  Currently it's set to Average, but that won't work.

Paul

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

I'd create a calculated field that does this:

SUM([Productive Hours])/SUM([Eligible Hours])

By doing this, your view will aggregate at the level of detail being displayed. Thus, it should handle both the detail rows as well as the summary row.

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

Thanks Ken,

Here are my current calcs for the Productive, Eligible and % fields:

Eligible Hours

ELSE 0

END

Productive Hours

If [Service Date]>= [Start Date]

AND [Service Date]<= [End Date]

THEN [zProd Set]

ELSE 0

END

Productivity %

ZN(SUM([Productive Hours])/[Eligible Hours])

I keep getting Aggregate/Non-Aggregate errors when I try and do something conventional.

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

Tough to troubleshoot without seeing an actual workbook...but, is there are a reason you have to do SUM([Admin Time]) in the first calc? Does that have to be aggregated at that point? If so, any chance of seeing a sample workbook, even with some fake data?

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

I'm not sure how your measure is being calculated - that may be key. I'd use a simple calculation as Ken suggested, and you shouldn't have to specify the total method - in fact, it should be left as Automatic. The only reason I can think of that this may not work is because you are just using a disaggregated measure such as [Productive Hours]/[Eligible Hours] which is typically not the right approach.

PS Ken Flerlage ... I had to check your profile to remember which Flerlage is the original... now you two are just a combined super-prolific dashboarding entity in my head... great.

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

Haha! Yes, Ben...it's just like when we were kids and no one knew the difference! But that's cool--I'm happy if I'm associated with kevin flerlage's work. It's pretty amazing for only six months of using the tool.

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

Alright, looking at these calculations, there's probably another cause, but it helps to identify the cause. If you assume that SOME rows have admin time which exceeds [Hours (wk)], then you will get a 0 for that row, BUT in aggregate (the total), these will NOT resolve to a 0, as the total of ALL [Hours (wk)] - SUM([Admin Time]) is greater than zero, and thus no single row should return a zero. As such, your numerator and denominator in that percentage are returning different values than the 165/575 you're seeing in your view.

Ideally you need to remove the aggregation or move it elsewhere in the calc.

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

I don't like Kevin's progress... He makes me feel bad about myself.

• ###### 8. Re: Percentage of Grand Total

Well, I'm sure he'll take that as a compliment.

• ###### 9. Re: Percentage of Grand Total

Here's my workbook, had to anonymize it.

• ###### 10. Re: Percentage of Grand Total

This is tricky because Eligible Hours is already an aggregate and you can't aggregate an aggregate. However, you can sort of trick Tableau by using an LOD. Create a new calculated field called "Eligible Hours LOD" as follows:

// Get hours for single employee.

{FIXED [Employee ID1]: [Eligible Hours]}

Because of the way LODs work, this will not be an aggregate. So, you can then change change your Productivity % to:

ZN(SUM([Productive Hours])/SUM([Elligible Hours LOD]))

This should give you what you need.

I'd suggest that you also add the Service Date Range filter to context so that it is calculated before the LOD calculation.

See attached.

• ###### 11. Re: Percentage of Grand Total

Ken,

Perfect.  Works like a charm.  Thank you kindly, for the prompt response and the efficient fix!

Paul

• ###### 12. Re: Percentage of Grand Total

Any time!