7 Replies Latest reply on Nov 28, 2017 9:35 AM by Nishant Dwivedi

# How to write an LOD calc that excludes one dimension but includes another on your sheet?

Hi Tableau Community,

I have automotive registration data that is broken down by car bodystyle (sedan, SUV, etc.) and city. Bodystyle and city are both dimensions and registration #s are the measure.

I need help understanding how to write the following calc:

I want to show USA bodystyle market share but I DO NOT want it to change if I include the city dimension on the sheet. In other words, if City and Bodystyle are on rows and USA bodystyle market share is the text marks, the sedan bodystyle market share should be the same on the line of New York: Sedan: 20% as Los Angeles: Sedan: 20%. I don't want it to calculate sedan registrations in New York as a percentage of the country. I only want sedan bodystyle as a percentage of total registrations countrywide.

I think I need to include bodystyle while excluding city but don't know how to do that.

Thanks,

Ava

• ###### 1. Re: How to write an LOD calc that excludes one dimension but includes another on your sheet?

Could you share a sample file?

-AV.

• ###### 2. Re: How to write an LOD calc that excludes one dimension but includes another on your sheet?

Unfortunately I can't because it's private data but in Excel it's very simple.

The columns are City, Bodystyle, Total # of Registrations

For example:

City: Bodystyle: Total # of Registrations

New York Sedan 5000

New York SUV 10000

New York Coupe 2000

New York Wagon 3000

Los Angeles Sedan 6000

Los Angeles SUV 11000

Los Angeles Coupe 5000

Los Angeles Wagon 1000

So if total = 43,000

I want it so if I said USA market share by bodystyle it would show the nationwide market shares by bodystyle even if "Cities" were also on the rows

New York Sedan 26%

New York SUV 49%

New York Coupe 16%

New York Wagon 9%

Los Angeles Sedan 26%

Los Angeles SUV 49%

Los Angeles Coupe 16%

Los Angeles Wagon 9%

• ###### 3. Re: How to write an LOD calc that excludes one dimension but includes another on your sheet?

I think below formula should work.

attr({exclude [City] : sum([Registrations])}) / total(sum([Registrations]))

attr({exclude [City] : sum([Registrations])})   ------ calculating regstraion by body type and excluding city.

total(sum([Registrations]))    -----  over all total ( 43K in this case)

• ###### 4. Re: How to write an LOD calc that excludes one dimension but includes another on your sheet?

Here it Is

Thanks

Deepak

• ###### 5. Re: How to write an LOD calc that excludes one dimension but includes another on your sheet?

Oh...Here it is..

• ###### 6. Re: How to write an LOD calc that excludes one dimension but includes another on your sheet?

Attachment

• ###### 7. Re: How to write an LOD calc that excludes one dimension but includes another on your sheet?

Hi Ava,