4 Replies Latest reply on Feb 3, 2017 9:10 AM by Shinichiro Murakami

# Sub-total calculation with hierarchy

hi every body ,

I  need to change the calculation of  subtotals by including a hierarchy.

I managed to recalculate the subtotals but roll back in hierarchy it puts me an error message.

I joins a workbook to explain my need.

To calculate the subtotel , i use this method (If you have another method I would be happy too, more dynamic )

IF MIN([Region])==MAX([Region]) THEN

IF MIN([Segment])==MAX([Segment]) THEN

IF MIN([Product])==MAX([Product]) THEN

SUM([Value])/[Segment Total]

ELSE

SUM([Value])/[Region Total]

END

ELSE

SUM([Value])/[Grand Total]

END

ELSE

SUM([Value])/[Grand Total]

END

• ###### 1. Re: Sub-total calculation with hierarchy

Hello balkis,

I would use LOD calcs rather than Table Calcs.  For instance - the 14.95% for the Appliances Sub-Category could be expressed as

sum( {Fixed [Sub-Category]:sum([Sales])} / {Fixed [Category]:sum([Sales])}  )

Other ideas for LOD calcs can be found here:  Top 15 LOD Expressions | Tableau Software   .  Check out example #4 and how the different levels are used there.  You might find this sort of  solution more easier to maintain while providing flexibility.

Patrick

• ###### 2. Re: Sub-total calculation with hierarchy

i fix this one but i have another issue with recalculating sub-totals.

if category have one sub-category the recalculating of percent of category is false .

please refer to screen shot for details .

thank you very much for your help

• ###### 4. Re: Sub-total calculation with hierarchy

Not sure I catch your issue correctly or not,

But if this is the result you are seeking, follow below pretty simple procedure.

Calculate sum of sales beforehand.

[sales sum]

sum([Sales])

Then put this both for abs value and % of total with using pane.

Thanks,

Shin

1 of 1 people found this helpful