9 Replies Latest reply on Sep 19, 2013 11:01 AM by Ryan Johnson

Sum at each change in

I feel silly posting this as it seems like this is a calculation even novice designers should be able to do, but I'm having some sort of brain freeze.  See attached Excel sheet.  How do I tell T to sum items at each change in Project?  Like for project 2754, the calculation would sum to 49.

Thanks!

• 1. Re: Sum at each change in

Tableau should do this for you automatically as it will treat projects as dimensions and items as measures. Dragging Project to rows and Items to columns should do the trick.

• 2. Re: Sum at each change in

Yep-just double clicking/bringing the field "Items" into your visualization, with Project on Rows or Columns, will group the sum of that measure by each project #.

• 3. Re: Re: Sum at each change in

Thanks, guys.  I have the feeling I did a poor job of explaining my end goal.  Sorry about that!  I've attached a sample workbook that hopefully better illustrates what I'm trying to do.  I'm trying to find the max of the sum of the items for each project.  So if project 2754 had 49 items total and project 2315 had 45 total, I'd want the max to show in the report as 49 for the Ohio - Furniture categories.

• 4. Re: Re: Re: Sum at each change in

I still don't understand.  If you place sum(Items) next to Category 1 and Category 2, it shows as 94

I created a table calculation using WINDOW_MAX in the attached--is this what you are looking for?  In this example, I had to right click on the Table Calc, choose "Compute Using" and list the dimensions in the order shown below (basically, from left to right in the order they are on the Rows shelf)

I hope this helps.  Table calcs can be tricky, but if your actual data is more complex than what you're sharing with us, this may be the approach you need.

• 5. Re: Re: Re: Re: Sum at each change in

Thanks Matt Lutton!  That's closer, for sure.  What I'm looking for is to have one row that would say "Ohio" "Furniture" and then the mark for Items would be 49.  49 because it's the max sum of items between the two projects. See attached.

Yeah, the actual data set I'm working with is pretty large.  This boils it down to its most basic form for sure!

• 6. Re: Re: Re: Re: Sum at each change in

I don't have Tableau in front of me, but wrapping it in a calc like:

IF INDEX()=1 THEN [FieldName]  END

Should display it in the first row of each partition.

1 of 1 people found this helpful
• 7. Re: Re: Re: Re: Sum at each change in

After doing some more digging on this, I think what I'm trying to do is find the MAX of a group of aggregated SUMs.  I tried to write a calculated field like: MAX(SUM[field_name])), but T gave me the error "Argument to MAX (an aggregate function) is already an aggregation and cannot be further aggregated".

Guess this is more advanced than I thought it was...!

• 8. Re: Re: Re: Re: Re: Sum at each change in

You're going to need to study Table Calculations, I'm afraid.  I've set up what I think you're looking for in the attached.  I've used WINDOW_SUM and WINDOW_MAX in this one.  I am just beginning to understand how this stuff works myself, so someone else may be able to explain it better than I can.  But hopefully, this can get you a start.

1 of 1 people found this helpful
• 9. Re: Sum at each change in

Thanks, Matthew!  I appreciate your help.  I'll keep plugging!