What's your data source? There are things Tableau was not designed to do easily. This is one of them.
The best and easiest option will be to make the changes at the data source level before bringing the data into Tableau. If you have a SQL database, you could use a union to add a new Product called "others" in the PRODUCT hierarchy. This will be equivalent to SUM(Product) in the CATEGORY Hierarchy partition minus SUM(Product) in the PRODUCT Hierarchy partition.
i agree with Ossai. Not as designed. However, I believe you can try writing SQL and add a union to create a new category. Open your excel file with legacy option that would give you three feature to write sql. A quick trick is to convert to sql from the menu . Then add a union and create a new category say other and see if you can create a calculated total may be using switch ( heard that case is not supported ) something like sum of product - sum of category. If this tricky then add a 0 you would have a placeholder where you have to use calculation field in tableu try o replace that 0 with the difference.
Sql or sql based reporting tools would be easy to do this sort of tasks.
It's an excel file, and I'm afraid my sql skills are non existent. It's syndicated data and so is limited at the detail it can be run. I'm trying to show a table or graph with % of total. So I need to come up with a way to show 100% of the category.
ok, so can I create a single element (chart, graph, table etc) from these 2 different sheets? I tried tinkering with a level of detail expression but just ended up confusing myself. This is my first attempt at merging about 20 different reports into 1 file. Not doing so great I'm afraid.
The problem you're trying to solve
is to have "yet another product" (YAP)
member in your 'PRODUCT' Dimension.
There is indeed such a member in your dataset already.
Guess who? It's the [Hierarchy] = 'CATEGORY' :-)
Having this dimension member in place
one could calculate aggregated Measures
using (not so fancy) LOD expression(s).
The fine point is to use the MIN() aggregation
for this member only, while using SUM() for the rest.
Please find the attached.
Hope it helps.
UNKNOWN PRODUCTS CALC_YF.twbx 64.8 KB
Thank you Yuriy. I am so new at Tableau, I am not even sure what terminology to use to ask my questions!
So I was on the right track with LOD expressions. Was there an attachment?
Christine, you're welcome.
Don't worry too much about terminology,
it is "invented" on-the-fly most of the time :-)
PS My previous post does have the attachment (workbook).
To see / download, please go visit this thread with a browser.
Yuri, Thank you so much. This is precisely what I was looking for. This will be a tremendous help as I
work to consolidate these reports. Thank you again and have a wonderful day!