4 Replies Latest reply on Feb 19, 2016 8:42 AM by Shinichiro Murakami

# Totals & filtering

Hi All,

I'd like to show the Totals for a table calc (% difference).  I'd also like to filter out the first column (NULL) which I usually do by putting the table calc in the filter and saying 'no null values'.  However, when I bring in Totals this doesn't seem to work.  Any ideas or workarounds?

See example attached.

Thank you,

Susan

• ###### 1. Re: Totals & filtering

The percent or average calculation is tricky.

100/110  ,  200/210  ,  200/300  ==> 10%, 5 %, 50%  ==> simple AVG = 21% which is table calculation's total  -(1)

Reasonable calculation is (100+200+200)/(110+210+300) = 3%  -(2)

I could not find the way to calculate (2) with table calc, then used LOD.    Overview: Level of Detail Expressions

and used parameter to swap Header and calculated field.

[G.TTL Profit Ratio]

{exclude [Segment]:sum([Profit])}/{exclude [Segment]:sum([Sales])}

[Profit Ratio Param]

if [show G.TTL]="Segment" then ([Profit Ratio]) else attr([G.TTL Profit Ratio]) end

if [show G.TTL]="Segment" then [Segment] else " " END

Thanks,

Shin

9.0 attached

• ###### 2. Re: Totals & filtering

Shinichiro,

This is an elegant solution, thank you.  My objective however is to have the segments + total shown together in the same table (with the first column filtered out).  Would you recommend creating a dashboard and stacking two tables, one by segment, one as total?

Thank you again,

Susan

• ###### 3. Re: Totals & filtering

Shinichiro,

I just realized that this is not an issue in v9.2 (I'm on 9.0)... interesting.  I guess buggy and was fixed?  In any case, I really appreciate your time and help.  The forum users are always such an amazing resource.

Susan

• ###### 4. Re: Totals & filtering

Yes,

9.2 has a couple of new feature, so it's understandable you don't see the issue.

I'm still not heavily using 9.2 yet, so you can verify and let me know.

Thanks,

Shn