8 Replies Latest reply on Jun 13, 2018 8:38 AM by Jim Dehner

# Creating a share measure vs a fixed line in my data set

Hi Everyone,

I am trying to create a share measure as a calculated field in Tableau. I have displayed below a very simple example with the sales for a category and 2 companies (A and B) - the value share for each company is the proportion of sales vs the category.

Value Sales
Value Share
Category250100
Company A10040
Company B15060

The only way I have been able to do this right now is using the following formula: (SUM([Value Sales]) / TOTAL(SUM([Value Sales])))*100

This formula is only correct however if the sum of company sales equal the category (as shown in my example above), however if there are company sales missing, using the above formula will not give me a true value share - see example below:

Value SalesValue Share using formulaCorrect Value Share
Category250
Company A1005040
Company B1005040

I need the formula to share against the category line and not against the total of all company sales.

I have attached a Tableau workbook with some very simple data - any help with this is greatly appreciated!

For context I need to create a number of share and index measures with a rather complex data set but I am hoping I can leverage any insight I can get on this simplified example as the principles should be the same.

Thank you in advance for any help with this!

Naila

• ###### 1. Re: Creating a share measure vs a fixed line in my data set

Hi Naila,

SUM([Value Sales])/min(

{ FIXED : sum(if ISNULL([Company]) then [Value Sales] end)})

Then format this field as a percentage, results will be this:

Thanks, I've attached the workbook so that you can take a look.

Mavis

• ###### 2. Re: Creating a share measure vs a fixed line in my data set

Good morning

it uses the following formula

and returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Creating a share measure vs a fixed line in my data set

I have tried to use this suggested formula for my complete workbook and it does not work, I think this is because I have a number of other attributes like category, channel and time period. I have attached an updated version of the workbook which takes these into account, are you able to incorporate this into your calculation to get it to work?

Thank you again!

• ###### 4. Re: Creating a share measure vs a fixed line in my data set

Hi Jim Dehner,

As with my reply to Mavis, is it possible to get your calculation to work with the additions I have added to my second file?

Thank you!

• ###### 5. Re: Creating a share measure vs a fixed line in my data set

Hi Naila Ahmed,

Ah you have filters, in that case please try this:

SUM([Value Sales])/min(

{ EXCLUDE [Company]  : sum(if ISNULL([Company]) then [Value Sales] end)})

I've added it into your workbook and it looks like it works!

:

Originally I was using a fixed LOD calculation which comes BEFORE any filters are applied. Now I've used an exclude LOD calculation which takes the filters into account

More information on LODs can be found here: Overview: Level of Detail Expressions  and Top 15 Tableau LOD Expressions (Practical Examples)

Thanks, I've reattached the workbook so that you can see how it works.

Mavis

• ###### 6. Re: Creating a share measure vs a fixed line in my data set

Good morning again

I see you added dimensions to the vis

the way fixed works it will use the dimension to create permutations of the all the combinations

if you take the formula I sent fits and add the dimension in your viz

it will produce this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 7. Re: Creating a share measure vs a fixed line in my data set

Thank you Jim Dehner

This seems to work

• ###### 8. Re: Creating a share measure vs a fixed line in my data set

Thanks