7 Replies Latest reply: May 11, 2017 3:00 PM by Martin Müller RSS

    Expression as share of total

    Martin Müller

      Hello Community,

       

      what I am trying to accomplish is to display the relative share of some value of a total. Here is a quick example:

      • Suppose I have companies as my dimension.
      • Then I have individual products (Product A, B and C) sold by all the companies.
      • In a bar chart (or basically any chart), I want to display the share of one product relative to total sales (i.e. of all products) of each company as the expression.
      • Lets say one example company has sold 100 units of each product. Then I want the bar representing this company to display 0.33 when I select a specific product.

       

      This seems trivial, but the problem is that total sales are not included in the selection anymore when I choose a specific product.

       

      For example, when I have product A selected, then SUM(Sales)/SUM(TOTAL Sales) will display the share of sales of product A for each company relative to total sales of product A (across all companies combined), rather than share of total sales of each company.


      SUM(Sales)/SUM(TOTAL <Company> Sales) will show the share of selected products relative to each other, e.g. when I select products A and B, and each one was sold 100 times, the formula will show 0.5 for each, instead of 0.66.


      How can I achieve that the formula refers to the total sales of all products by each company?



      Thanks a lot!