in % relation column table top 5

[#1]

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!

[johnw · Accepted solution]

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).

[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