In the simplest case, your calculated fields would look like this:
[Event Sales - Return of Event Sales]
SUM( IF [Sale Type1] = 'Event Sales' THEN [Amount] END ) - SUM( IF [Sale Type1] = 'Return of Event Sales' THEN [Amount] END )
This simple aggregation calculation will automatically honor any filters/segments you apply to your view.
I should have specified that in my actual workbook the amount field is an aggregated calculations SUM(sales quantity * unit price) so the calculation you have provided me indicates that I cannot mix aggregate and non aggregate comparisons or results in an IF expression.
Sales Type is a string as well.
Seems like your calculation could be done at the row level, and doesn't need to be an aggregation. What if you rewrite to it be simply:
[Sales Quantity] * [Unit Price]
Then does the it work in the context of the calculation I provided above?
Yes this has worked. However, I have to change the - operator to a + in order for to actually subtract?
SUM( IF [Sales Type] = 'Event Sale' THEN [Sales Amount] END )
SUM( IF [Sales Type] = 'Return of Event Sale Item' THEN [Sales Amount] END )
This actually subtracts correctly. Weird but hey it works.
It gets messy if you have a different calculation for each Sales Type. Each calculation would look something like this:
Discount Sales Initial
IF [Sales Type] = "Discount Sales" THEN [Amount] END
Discount Sales Return
IF [Sales Type] = "Return of Discount Sales" THEN [Amount] END
If you had these written out for each Sales Type, you could do 3 different calculations that follow this convention:
Final Discount Sales
[Discount Sales Initial]-[Discount Sales Return]
I think what you really need here though is a data restructure. Think of 3 rows being Event Sales, Discount Sales, and Regular Sales. The columns would be Sales Type, Amount, and Return Amount. The data is easier to interpret, and Tableau can whip up a visualization like nothing. Plus this allows for future expansion into new sales types as well. Perhaps they begin tracking Membership Sales, for example. The correct data structure would allow for easy management, rather than having to write a whole bunch of additional calculations. If you can't restructure your data for some reason, is there any other identifying fields you're not mentioning? Anything that could serve as an ID field on which to pivot your data?
Ah, it sounds like your return line items are already negative. I didn't have visibility on the original data, so I made the assumption everything was absolute values.
Glad it's working for you!
Yes, there is an ID field that identifies each sales type.
For instance 15 = Event Sales
How would I be able to use this to pivot the data because I agree it sure would be nice to have it structured as you had mentioned.
Do the ID fields match for "Regular Sales" and "Return of Regular Sales"?
No each are given its own ID #
Regular Sale = 12
Return of Regular Sale = 13