I've created a map of raw materials that we use to produce finished products, then linked the raw materials used to the revenue and margin generated from the sales of those products. Each raw material is linked to the total revenue from the finished product where it is used, regardless if it represents a 10% or 50% of the ingredients used, as without any of the ingredients you can't get the finished product.
Due to the structure of the analysis I had some raw materials appearing several times in the production of the same final product, so to show correctly the revenue/margin I used a Fixed LOD at finished product level.
Now I want to show in a Pareto chart the total revenue/margin associated to each ingredient but also the % of total. The issue is that since the values shown at ingredient level are not exclusive - given that the same finished product is produced using several ingredients, and each of them is associated to the entire revenue / margin of the product - when I try calculating the grand total of the Fixed LOD Measure, the sum is lower vs the sum of the individual members. Thus I cannot get a proper % of total for each ingredient that ends up with a running total of 100%.
Any idea of how to solve?
This sounds like a fun problem to tackle. Would you have a Packaged Workbook for the community to share some sample data so we have a better idea of the data used and the calculation.