In Tableau, you can do calculations on discrete records and use them in aggregations. You'd first create a calculated field that manipulates the data in some way (simplest thing I can think of is a calc like -[Sales] to return the negative amount of [Sales], and then if the default Tableau aggregations Sum/Count/etc. don't work then you can create your own calculated fields. Beyond that, there are table calculations for further aggregation.
In other words, you can create calculations at any of the following levels (and combine them together into a single calculated field, with some limits):
[Sales] - acts on discrete records
SUM/COUNT/etc.([Sales]) - aggregation determined by what discrete (blue pills) are on Columns and Rows shelves, and what's on the shelves on the Marks card
WINDOW_SUM/etc.(SUM/COUNT/etc.([Sales])) - table calc aggregation determined by what's in the view and what you specify for addresssing and partitioning
The "gotcha" with Tableau is that when mixing calculations on discrete records and then aggregating those results in the same view, you need to make sure that you aren't returning multiple values for each displayed mark. For example, if I have an IF/THEN statement like IF [Sales] < 100 THEN "Under $100" ELSE "Over $100" END and then I put that on the Color shelf in a view that aggregates by Product Category, I'll get overlapping colored marks. So then you need to do one or more things to make the calculation work, the simplest is to use SUM([Sales]) for the comparison. I've attached a workbook showing this.
discrete vs. aggregate.twbx.zip 170.8 KB
Also, it may be necessary to invoke Analysis -> Do Not Aggregate Measures in the menu , so that the calculations can be performed and then the results aggregated. But try the default setting first.
Just wanted to pop in and say this thread was really helpful. Thanks.
Just wanted to pop in and say this thread was really helpful. Thanks. x