Though your example is overly simplified,
it took some 'acrobatic' moves to calculate
BOM (Bill of Materials) -- even in Tableau.
I believe that Tableau is not the tool of choice
for the tasks like this. The common approach
to the problem of flattening (ragged) hierarchies
is to use some flavour of recursive algorithm,
of which Tableau has none (well, almost).
In the simplest example (as yours) it is the Tableau Desktop user
who is looking at the data and deciding upon the (hierarchy) levels,
then joining the source table as many times as needed (two joins here).
Please find the attached.
Ingredient_Example_v10.2_YF.twbx 30.5 KB
indeed I had the impression Tableau is not the perfect tool for this but since I lack VBA expertise I hoped there could be some trick to get it done in Tableau directly, where I plan to build the data visualizations.
Thanks for your reply, it's helpful!!!
Cristiano, you're welcome.