7 Replies Latest reply on Sep 18, 2018 10:22 AM by maneesh.gaddam

# Aggregate by type

Hello everyone.

Been playing with Tableau for a week, and it looks like a great tool, yet the learning curve is still high above me.

I have a dataset like this

typevalue
Field11
Field22
Field310
Field...4

I need to make a bar chart where every column represents the sum of values from value column by custom logic. That is:

Bar1 = SUM([value of Field1], [value of Field2], [value of Field3], [value of Field...])

Bar2 = SUM([value of Field2], [value of Field3], [value of Field...])

Bar3 = SUM([value of Field3], [value of Field...])

Bar... = SUM([value of Field...])

What would be the best way to achieve that? I created a bunch of calculated fields, but that looks like a mess to work with. Is it possible to do with just one calculated field or some other approach?

• ###### 1. Re: Aggregate by type

Alexey,

You can place [Bar] on the rows and have the following calculation for values and put it in the columns.

WINDOW_SUM(SUM([Value]))-IFNULL(LOOKUP(SUM([Value]),-1),0).

Windows_Sum calculates the total and Lookup subtracts the previous value from total.

1 of 1 people found this helpful
• ###### 2. Re: Aggregate by type

Hi Alexey,

Just do a running total  on the measure to get this

Let me know if this is what you were looking for.

1 of 1 people found this helpful
• ###### 3. Re: Aggregate by type

Thank you, that was a nice hint. The calculation that you provided returned somewhat incorrect result, but my random poking into its variants yielded exactly what I needed.

I have but a dim idea why it works though, so if you caould do a bit of extra explanation, that would be totally awesome.

Here is the raw data by category (aggregated):

Here is what your calculation did:

And I finally came up with this:

WINDOW_SUM(SUM([value]), 0, LAST())

• ###### 4. Re: Aggregate by type

Thank you, I was immediatly able to get the funnel-like thing that I wanted.

Drew this thing with it:

The only problem, it is sort of inverted to what I intended, is there a way to make it go from top to bottom? With yellow row staying the largest, and green the smallest at the bottom?

• ###### 5. Re: Aggregate by type

Alexey,

Right click on the axis and select Edit axis and check the 'reversed' option on the bottom left on the pop-up. It reverses the axis.

• ###### 6. Re: Aggregate by type

Maneesh,