4 Replies Latest reply: Nov 21, 2017 2:20 AM by Luis Madriz RSS

    Keep Totals constant

    Kenneth Holden

      I have a table with sales reps as dimension and a variety of measures such as units, sales, averages etc

       

      at the top I have the total line set to averages.

       

      However when I filter by one rep the averages change to match the one reps average and so I cannot show her how she compares vs the averages of all other reps combined. 

       

      I want to know how to keep the Total row as all the reps averages, but when I filter by one rep have the Toal row remains constant in values for all the reps averages.

       

      is this even possible?

       

      Thanks.

        • Re: Keep Totals constant
          Luis Madriz

          Hi Ken,

           

          I haven't seen totals in the same table behaving like that...

           

          Maybe creating a new avg measure that doesn't get affected by a selection. If you don't want it on the table you could build a KPI and put it away from the table? AVG({<SalesRep=>} Sales)

           

          Just an idea,

           

          I hope this helps,

           

          Luis

          • Re: Keep Totals constant
            Shraddha Gajare

            Please share sample app

            • Re: Keep Totals constant
              Celambarasan Adhimulam

              May be you can try the Expression as

              If(Alt(Rowno(),0)=0,

                   Avg({<SalesRepFieldName=>} Units/Sales),

                   Avg(Units/Sales))

               

              Thanks

              Celambarasan

              • Re: Keep Totals constant
                Luis Madriz

                Hi Ken,

                 

                I learned about another function that could also help you.

                 

                If(Dimensionality()=0,

                     Avg({<SalesRepFieldName=>} Units/Sales),

                     Avg(Units/Sales))


                I hope this helps

                 

                Cheers

                 

                Luis

                 

                When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others