1 2 Previous Next 16 Replies Latest reply on Apr 8, 2011 12:16 AM by guest contributor

# How to sum all elements in a dimension - and then display this sum together with those elements?

Hi - I have a dimension in my Tableau workbook called 'Country'.

It has elements for European countries - FR, UK, SP, PL, BE, CH, RO, SK, PT, AT ..

When I put this dimension into the column section of a workbook, it shows columns for each element.

If I add Row Grand Totals, or Group the elements then I get a total for all Countries.

I'd like to create a separate element 'Europe' = UK+FR+SP+PL+BE+CH+RO+SK+PT+AT. I'd also like to create other elements that did not all up all elements (e.g. Top4 = UK+FR+SP+PL)

I'd like to be able to have columns with Europe, Top4 as well as the other elements of Countries (if I Group them I no longer see the individual elements as well; if I choose Row Grand Totals I get the total of all columns and not part of them ..

Is there a way to create such elements that represent a Grouping of elements in a dimension and then display them in a table together with the sub-elements?

• ###### 1. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

I believe what you are looking for can be done with table calculations.

Can post some sample data that represents your situation, and maybe an Excel spreadsheet that shows what you want the end result to look like?

• ###### 2. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

Hi - here's a Tableau Workbook and Excel file with some data showing what I'd like to do.

There is a worksheet showing Quarterly Volumes for each Country, another sheet where I have grouped 4 countries together, and a final where I show total volumes per Quarter.

I'd like to be able to have all 3 charts merged together .. ie show Quarterly volumes per Country + Top4 + Europe all as one.

Is there a way to display elements of a dimension together with calculations derived from those elements?

Thanks,

Andrew

• ###### 3. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

How do you want to calculate the top 4?

- overall top 4, based on sum of volume for all quarters, the top 4 will be the same for each quarter

- top 4 for each quarter, based on sum of volume for each quarter, the top 4 could potential change each quarter

- some other way

• ###### 4. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

How about something like the attached?

I found you can calculate the top 4 for each quarter and the other values in one calc field. I you want it some other way, or based on the overall sum, it would require another calc field.

• ###### 5. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

Hi Joe - this is great .. but I'm not quite sure how you did it.

You have added a new duplicated data sheet with a new dimension called Line - this includes new elements called Europe & Top.

How did you create these ?

Thanks,

Andrew

• ###### 6. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

I used custom SQL to duplicate the data, one set for each individual country, another for Europe, and one for Top. With the new data source selected, from the, and from the menu, you select Data->Data Connection->Edit, you can see the custom SQL that I used.

• ###### 7. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

Thanks a lot ..

A bit trickier than I expected but it will do what I need. Seems a bit strange that you can't easily do a sum or average of all elements in a dimension and display the result together with those elements ..

Andrew

• ###### 8. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

There are other methods, available depending on your constraints.

The method I used here is my preferred approach because I like the layout and control it allows for. You can also do some of this with a dual axis multiple mark type.

• ###### 9. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

Hi iam a beginner to tableau,Please help me with the below request

I have one  dimension CUST_TYPE which in turn consists of buyer and seller which i created using Custom sql.I need to add a third category CUSTOMER to cust_type dimension which should be the sum of buyer and seller. How can we complete this action?

• ###### 10. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

also we can change the grand total header or can we add a alias to that column grand total

• ###### 11. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

can you provide a sample data file that represents your situation? And what you want for a result from that file?

• ###### 12. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

HI joe,

I have attached a sample data. Where the buyer and seller falls under a single dimension which i created using custom sql i want to add one more category as customer= buyer+seller..we need to add that dimension

• ###### 13. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

Here is the e.g:

METRIC A        10    15    seller+buyer

METRIC B        20    24

METRIC C        30      25

METRIC D        15      30

sorry for the previous comment posted without the attachment

. Ignore that. Here the buyer and seller falls under a single dimension which i created using custom sql i want to add one more category as customer= buyer+seller

• ###### 14. Re: How to sum all elements in a dimension - and then display this sum together with those elements?

I am unable to understand what you are asking for, nor what you have provided.

I am unable to parse what you have there. is that your data source, or a cross tab created in Tableau? That is not a well structured data source.

I would recommend reading over http://www.tableausoftware.com/support/knowledge-base/preparing-excel-files-analysis

Then maybe a thread like http://www.tableausoftware.com/support/forum/topic/need-help-line-graph would help.

1 2 Previous Next