Tracy, that kb article does not do what its title claims. It does not show methods to remove data, it only explains multiple ways to perform an average. This would only help in the simplest cases.
The situation here needs an aggregate of an aggregate. What is going on it the data is being joined/blended at one level, and then desired to be aggregated to another level.
If you can provide a sample packaged workbook (.twbx) that represents your situation, an example can be created for you that shows how to accomplish what you are asking for.
That would be great!! It's a bit more complicated than just the two tables - there are actually a few other ones I need to go through, so basically it's:
orders -> order_recipients -> order_items -> products
I'm attaching a sample workbook - you can see when you look at the order IDs in the underlying data that there are duplicate orders (since they have different order_item IDs).
Sales by Product.twbx.zip 8.5 MB
I see pricing type comes from the product table, and the measures you are calculating all come from the orders table.
For each order there is a single value for all measures of interest (thus far), but there are multiple pricing types in some orders.
What business logic would you like to use when looking at a single pricing type? Is that what you want to look at the net/gross/etc for order that contained that pricing type? or would you like finer grained view?
How about the attached?
I was hoping to have sales by day and put pricing type as a filter so I can select "BookPricing" and see sales from books, "CardPricing" and see sales from cards, etc, but if I select "all" I just see total sales from distinct orders. I think that is what you may have done, with the calculated field looking at the average price.
What were the steps you took exactly? I'm trying to play around with it but I'm confused as to how you arrived at some things, like "Multiple Fields" for example. Or "IF FIRST()==0 THEN" in the calculated field formula.
Thanks for the help!
I do not believe your data is stored at the granularity to answer the question you have.
You have 5393 order ids with two pricing types, and 46 with all thee.
Your numeric measures are at the order id level, but you want to look at it at the pricing type level, and that cannot exactly be done with your source data. From what I can tell, this is not a limitation of Tableau, it is a limitation of how you are storing your data in your database. You may want to have a conversation your your database manager about your question.
As for the table calculations, you can find many examples of them on this forum. The "IF FIRST()==0 THEN" condition is an effort to show only one mark per partition, this is the route that I normally use, but there are many other options as well.