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

    Totals & filtering

    Susan Oner

      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,



        • 1. Re: Totals & filtering
          Shinichiro Murakami

          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


          [Segment Header]

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





          9.0 attached

          • 2. Re: Totals & filtering
            Susan Oner



            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,


            • 3. Re: Totals & filtering
              Susan Oner



              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.



              • 4. Re: Totals & filtering
                Shinichiro Murakami



                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.