8 Replies Latest reply on Aug 26, 2013 7:30 AM by Waldemar Holderbein

# How to create a dynamic Share Calculation

Hi everyone,

happy to join the Tableau Community. I'm facing a little problem. Maybe someone is able to help me

I have two Quick Filters (Product, Market) and a measure sales.

What I try to do is, to calculate a total, which depends on the selection in the Market Filter, and calculate based on this total

a share of sales based on the selection in the product filter.

Attached is a dummy dashboard.

Hope someone can help me.

Best regards

Waldemar

• ###### 1. Re: How to create a dynamic Share Calculation

Waldemar,

You need to use Parameters.  By creating a Market parameter as your filter instead of the dimension Market you will be able to filter sales by Market and by Product in a more effective manner.

I have updated your example workbook with the correct way to use this parameter in your data.  I hope this helps.

Tom

• ###### 2. Re: How to create a dynamic Share Calculation

Hi Tom,

thanks for your answer and help. I think I should explain my r equirement a little bit more.

1) The total market should be calculated based on the selection in "Market"e.g. If the market is "a" the total market should be the sum of sales in market "a". If I now pick a product "b" the SoM should be the sales of this product/total market. In the current settings the total market is always determined through the Product selection.

2) Ideally, this works for 5 different Markets. Market 5 is a submarket of Market, Market 4 a submarket of 3 etc.

3) I took Quick filters instead of Parameters becaus of the possibility to show "only relevant values" in the lower markets.

Hope this clarifies my problem

Kind regards

Waldemar

• ###### 3. Re: Re: How to create a dynamic Share Calculation

Hi Waldemar,

I have created separate sheets for Total and SoM. Applied only the Market filter on the Total sheet. Included both the sheets in a dashboard.

Please check if it works for you.

Regards,

Prashanth

• ###### 4. Re: How to create a dynamic Share Calculation

Hi Prashanth,

What's still not working is the product selection.

As soon as I select e.g. product "a" or "b" the SoM is 1. And this is the problem I'm facing all the time.

It should be displayed the SoM based on my selected product and the Market I chose.

Kind regards

Waldemar

• ###### 5. Re: How to create a dynamic Share Calculation

Wouldn't you just need to set SoM to compute using "year" instead of "product" or am I not understanding the purpose of SoM?

• ###### 6. Re: How to create a dynamic Share Calculation

Hi Tom,

I thought so, too. But if I would do so, I had the same total value for every year.

I try to explain it different.

I have two elements which determine the SoM.

1) The Sales of my Product which I choose in the Filter drop down.

2) The Sales of the Total Market. It's basically a sum of all the products which belong to a certain market.

These two elements are always dependet on the time. That means if I display by calendarweek, the Sales of the product and the total market differs from week to week.

My problem is,

If I pick a Product, the total market is directly cut down to just this one product instead of my selection in the Market filter/parameter.

I can't find a way to tell Tableau "Total Market = based on Market Selection, ignore Product filter"

• ###### 7. Re: Re: How to create a dynamic Share Calculation

Ok, I found a workaround but I don't know if you'll like it.  It goes back to using parameters which means you won't be able to use the quick filter show only relevant values but it is the only way I could get this calc to work.

Check out the attached workbook. What I did was create a ProductFilter Parameter.  I then created three calculated fields.

ProductSales

IF [Product] = [ProductFilter] THEN

[Sales]

END

Total Sales

SUM([ProductSales])/TOTAL(SUM([Sales]))

Products (Dimension)

IF [Product] = [ProductFilter] THEN

[Product]

END

So then you throw ProductSales where sales would be and Total Sales where SoM would be and Products where Product would be.  Show the parameter "ProductFilter" and then quick filter your market.  The only other thing you need to do is in a crosstab it is going to show you the "Null" Row.  You will want to hide this row.  Don't exclude the Nulls or it won't work.

1 of 1 people found this helpful
• ###### 8. Re: How to create a dynamic Share Calculation

Hi Tom,

I had hoped that there would be a way without using the Products as a Parameter.

In my case, I have a Productlist which contains 10.000 items. A Parameter without the show relevant values is quite a mess

Nevertheless, I thank you very much for helping me. I really appreciate your help.

Kind regards

Waldemar