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

# Expression as share of total

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!

• ###### Re: Expression as share of total

Try a combination with set analysis:

SUM(Sales)/SUM({<Product= >} TOTAL<Company> Sales)

• ###### Re: Expression as share of total

Hi Stefan,

could you please elaborate a bit further on your suggestion? What exactly is the bold expression doing compared to the rest?

• ###### Re: Expression as share of total

{<Product= >}

will not consider any selection made on the Product filter i.e. it will ignore the Product selection while calculating the sum of sales

• ###### Re: Expression as share of total

Unfortunately, it also does not work. It says no data to display...  Adding a specific product after the = sign in your bold expression ( {<Product = 'A'>} ) also does not work.

Kind regards,

Martin Müller

• ###### Re: Expression as share of total

It's always helpful to look at a specific sample to get the syntax right and also to state the context and expected result.

A test data set for example could look like:

LOAD * INLINE [

Product,Company, Units

ProdA, CompA, 100

ProdB, CompA, 100

ProdC, CompA, 100

ProdA, CompB, 100

ProdB, CompB, 200

ProdC, CompB, 300

];

Then a table chart with no selections could look like

Company Product Sum(Units) Sum(TOTAL<Company> Units) Sum( Units) / Sum({<Product= >}TOTAL<Company> Units)
900 900 100%
CompAProdA10030033%
CompAProdB10030033%
CompAProdC10030033%
CompBProdA10060017%
CompBProdB20060033%
CompBProdC30060050%

And with ProdA selected in field Product:

Company Product Sum(Units) Sum(TOTAL<Company> Units) Sum( Units) / Sum({<Product= >}TOTAL<Company> Units)
200 200 22%
CompAProdA10010033%
CompAProdB01000%
CompAProdC01000%
CompBProdA10010017%
CompBProdB01000%
CompBProdC01000%

Or using only Company as dimension:

Company Sum(Units) Sum(TOTAL<Company> Units) Sum( Units) / Sum({<Product= >}TOTAL<Company> Units)
200 200 22%
CompA10010033%
CompB10010017%
• ###### Re: Expression as share of total

Thanks for your help. In the end I used a slightly different expression, but your hint towards set analysis led me to this presentation, which helped a lot to understand the syntax and methodology behind it.

Best,

Martin