8 Replies Latest reply on Feb 3, 2014 2:13 AM by . Indumon

How to calculate Average/Sum of de-selected data source?

I have a data source of 80 companies. For each company there are a lot of operating figures for each year: profit, costs, sales, employees …

In tableau you can choose the companies and years by multiple values for every figure. This will list in a simple table or chart (rows=companies, columns=years, and operating figures = measures).

Now, I would like to show the average or sum of a operating figure for the complete data source, not only the chosen companies in tableau. So you would be able to see the average of a operating figure for the whole industry (each year) even though you only chose two or three companies.

How can I get this?

• 1. Re: How to calculate Average/Sum of complete data source?

I'm not sure I completely understand, but if you are wanting to filter by company without affecting the results in your view, this method may be useful to you:

A Jedi (Filter and Table Calc) Trick | Tableau Software

• 2. Re: Re: How to calculate Average/Sum of complete data source?

Hi Matthew,

thanks for quick answer. Please look at the screenshots, maybe they desbribe my problem better than I did it. In Screen_1 are all Companies selected. And so I easily get the right Grand Total for the complete operating figure (red marked). But usually I selected only fewer Companies (screen 2) for better view and compare. But in this selection I still want to know how is the total sum or total average from all companies in this year and measure.

Thanks

• 3. Re: How to calculate Average/Sum of de-selected data source?

Is there anybody out there? I really need help for my request.

• 4. Re: How to calculate Average/Sum of de-selected data source?

Hi Math,

As Matthew suggested table calculations should work in this situation.

If you can upload some sample data in excel I can create a mock up workbook for you

Regards

Indumon

• 5. Re: Re: How to calculate Average/Sum of de-selected data source?

Hi Indumon,

thanks for cooperativeness.Please look at the file. Let me know if the informations are sufficient

• 6. Re: Re: Re: How to calculate Average/Sum of de-selected data source?

Hi Matt,

Apologies for the delay, please find the attached sample workbook

Trick is company filter is created by the Table calculation lookup(Attr([Company],0).

Regards

Indumon

• 7. Re: How to calculate Average/Sum of de-selected data source?

Hi Indumon,

thanks for Example. But I can't open it on tableau public. error: "The requestes workbook does not exist on Tableau Public. Only workbooks that are currently available on Tableau Public may be opened".

Do you know how I can open it?

Thanks,

Math

• 8. Re: How to calculate Average/Sum of de-selected data source?

Ok, I will try to explain the steps based on the image you have attached in the previous post.

1.Click on Dimensions right corner drop down and select Create Calculated Field

2.In the formula area please put Lookup(Attr([Lebensversicherer]),0) then give a name to the calculation and save.

3.Now remove the existing "Lebensversicherer" filter and place the newly created calculation into filter.

hope this will help ..

Regards

Indumon