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,

       

      Susan

        • 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

           

          Thanks,

          Shin

           

          9.0 attached

          • 2. Re: Totals & filtering
            Susan Oner

            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
              Susan Oner

              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
                Shinichiro Murakami

                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