2 Replies Latest reply: Jun 20, 2016 6:06 PM by Sunny Talwar RSS

    in % relation column table top 5

    Florian Wadeh

      Hi guys,

       

      so I have a table comparing my top 5 salesmen in units. My Dimension is name_salesperson and the expression for my measure looks like this:

       

      Sum({$<value_type={"units"}, name_salesperson={"=rank(sum(VALUE)>5)"}, product_type={"chairs"}>}VALUE)

       

      This works fine.

       

      Now I would like to add a column measure showing the ratio of their sold units relative to all sold (chair) units. My expression looks like this:

       

       

      (Sum({$<value_type={"units"}, name_salesperson={"=rank(sum(VALUE)>5)"}, product_type={"chairs"}>}VALUE))

      /

      (Sum({$<value_type={"units"}, name_salesperson={"*"}, product_type={"chairs"}>}VALUE))

       

       

      Unfortunately this comlumn gives me a 1 (or 100%) for each salesman. The inserted pic is a example of what I would like to see in the end. (Im using the rank function so i can also display other salesmen, that I'd like to compare to my top 5 by the way.)

       

      Any help is appreciated!

       

      exampleofinpercent.png

        • Re: in % relation column table top 5
          John Witherspoon

          You may just need "total" before the last "VALUE".

           

          ...chairs"}>} total VALUE))

           

          Since name_salesperson is a dimension in your chart, your expression is still evaluated in that context, and so your /sum() is always for a single salesperson. Using "total" tells that sum to ignore the chart dimension(s).

            • Re: in % relation column table top 5
              Sunny Talwar

              In addition do you have null in Salesperson field which you are trying to ignore from the total on the bottom? If not, then you can might not really need this (name_salesperson={"*"}) in the denominator?


              Try this:


              (Sum({$<value_type={"units"}, name_salesperson={"=rank(sum(VALUE)>5)"}, product_type={"chairs"}>}VALUE))

              /

              (Sum({$<value_type={"units"},product_type={"chairs"}>} TOTAL VALUE))

               

              John's suggest is in Green above.

               

              HTH

               

              Best,

              Sunny