I'm not the greatest with SQL, Alex Kerin may have some insight on this--I think this logic can be used in Tableau's SQL.
From a the standpoint of not using SQL in Tableau, I would probably reshape the data so that Sales and Profit were each in their own columns. Then, a calculated field could be created similar to the following and place Invoice ID on the rows/columns shelf:
The following KBs gives more details about how to set up an Excel sheet and using the Re-shaper Tool:
Hope this helps!
I understand your point. Unfortunately I doubt that passing by Excel would be an option since we have a quite significant amount of data to treat. This being said, that operation can easily be performed in SQL, and with a lower cost.
I guess that my question comes back to how does Tableau deal with aggregation. I was asking a more experienced colleague earlier about how to address multiple-aggregation challenges (see below), and all his answers boiled down to actually have some other tool (being Excel, SQL or else) do that part of the job, and then let tableau take care of the visualization. If this is correct, and please feel free to jump in if not, I must admit that I am a bit disappointed, since this would be a piece of cake in several competing BI tools.
Would you see how to deal with this without any pre-treatment in SQL:
I have in my dataset geographic regions, each one connected to several customers (e.g. 100 customers in London), each one having several Invoices (e.g. ABC Ltd. has bought 10 products). I want to be able to differentiate:
- the average selling price in a region: SUM(All invoices in that region)/COUNT(All invoices in that region)
- the average selling price, to customers, in a region: AVG(SUM(Invoices of each customer, separately)/ COUNT(Invoices of each customer, separately))
Obviously the two will give a very different example since the first one only factors the volume of sales, whereas the second considers that all customers must have the same “weight” in the final average.