Hi Scott,

I had a similar requirement, so this is what I did and hope this helps you answer your question.

I have a Dimension = Account Sub Type under which I have an attribute 'Fuel Type' under which at Account Group Level this is broken down to 'Gas' and 'Diesel', I would like to calculate gallons sold at each level, so I created a parameter like this (I added All for my aggregation), In your case it would all the individual deals. For this do 'Add from Field' in addition to existing fields add 'All'

Then created a Calculated field like this, I have this on filter and set to 'True'

I use the parameter to control what I'd like to see, I also have measures that give me the actual numbers.

Thanks,

Shiva.

Scott, you'll have to trigger the Parameter by calling it in the calculated field and add this calculated field to Filter. Then right click on the parameter and select 'Show Parameter Control'. So, if you look at screenshots in my previous reply- you'll notice how I called 'Gallons Sold Parameter' in the calculated field. Hope that helps.

Scott- Could you mock up some data and attach a sample workbook?

Scott -- There is no "ALL" function (or even an "All" value) for filters.  You have to create some calcs to know what's happening on the sheet.

{ FIXED : COUNTD([Deal]) }

This will tell you how many Deals are in your database.

{ EXCLUDE [dimension 1](, [dimension2], ... [dimension last one on sheet]) : COUNTD([Deal]) }

This one will tell you how many are in the underlying table for that sheet (which essentially tells you how many were selected by the filter.)  Put all the dimensions that are on the sheet in that exclude list.

If calc 1 = calc 2, then the user selected All.

I did this on your sheet.  See values in the title of the sheet.  See attached.

I'm going to answer this in two steps.

The first is to show you sheet swapping.

Usually when you want to show things one way for ALL and another way for "some", make two sheets, and swap them based on the condition.

I made a copy of your original sheet.  I took off [Deal Name] from Rows, and inserted "Aggregate" in there.  Since [Deal Name] is not partitioned on the sheet now, the value is just the sum of all deals.

then I made a calc to compare the two numbers I previously created.  If they are equal, then calc = 1 else calc = 0.  (See [Display Aggregate?].)  I put this as a filter on both sheets, and for one sheet I select value = 1 (thus, ALL is selected), and for the other I selected value = 0 (meaning less than ALL is selected.)  Then I put both sheets on Dashboard 1.  Oh, and I did "Apply to" for the [Deal Name] filter so that it controls both sheets.  Play with the filter on Dashboard 1.  You'll see how this works.

GO to Dashboard 2.  Here I made a floating container and dragged both the sheets into that container.  Since only one displays at a time, they "swap" in place within the container.

That's sheet swapping.

See attached.

If you have 20 sheets, then sheet swapping would involve 40 sheets, and maybe you don't want to maintain that.

In the attached, see Sheet 1(3).  Here I made a calc called [Deal].  If we have ALL, then the value of [Deal] is "Aggregate".  For all rows.  Otherwise propagate the [Deal Name] into [Deal].  I put that on ROWS instead of [Deal Name].  Take a look at how [Deal] works on sheet (3).  (Don't worry about the multiple values yet when it's "Aggregate".)  I also created a new Balance calc to sum up all the Deal values when it's aggregate.

You'll notice that on Sheet (3) the window sum is just propagating the values from the individual deals.  Go to Sheet (4).  Here I change the way the table calc works so that it addresses along the [Deal Name] dimension.  Note:  I have to have [Deal Name] on the details shelf so that the dimensionality of that field is maintained on the sheet.  The [Deal] calc ends up as a measure because the [Deals on sheet] calc has to be a measure, and it's part of what makes [Deals].  And the presence of [Deal Name] on the sheet forces 5 values into the "aggregate" cell.  (It's just how Tableau works.)

Anyway, I made the table calc [Swap balances] to run along [Deal Name]. and now we get the proper value for the balance.  (But we still have 5 instances of the value.)

Go to Sheet (5).  Leave all 5 [Deal names] selected when you go to sheet 5.

Here I added another calc, [index] to the sheet on the filters shelf.  I edited that table calc to run along [Deal Names]. and then I edited the filter to select only for value = 1.  Now we get only the first instance of the 5 that were displaying for "aggregate'.  But there is a catch here.  If we select one less [Deal Name], that same filter only displays the first [Deal]!  SO I addressed that in sheet 6.

In (6) I dragged [index] to the detail shelf and added one more calc to control whether I want to allow only one value or all values of index.  Take a look at [Display 1 Aggregate].

Now the sheet behaves the way you want.  It takes a bunch of table calcs to control this (which is why it's easier to sheet swap), but now you don't need double the sheets.