4 Replies Latest reply on Jun 27, 2017 5:58 AM by paula.munoz

    Help with aggregated values as fixed values




      I'm having some difficulties trying to use the results from a table calculation on a different sheet where I have to do some filtering... In other words, I want to use the results from first table calculation as fixed values when I'm working on second worksheet.


      Below is an example with superstore data


      1. Lets say I created two calculated fields for manufacturer, in this example I'm only using Manufacturer Microsoft, but the idea is that there would be a an IF statement for each manufacturer:






      2.  Then I created a third calculated field called "Per Quantity"


      At the end my spreadsheet which is broken out by state, sub-category and accessories looks like this:

      3. Now I want to know the Total for Manufacturer Miscrosoft, for subcategory Accessories based on my manufacturer_cal (Sum across all states): and I get the value of $100.80


      4. Now I want a separate sheet where for example I filter by state and calculate a new Total for Manufacturer ("Total_Manufacturer2") 

      Example: Arizona:


      So for Arizona the quantity is 11, and manufacturer_cal is 9.90, what I want is to create a calculated field where I divide 11/$100.80 where $100.80 was the sum of that subcategory and manufacturer across all states.


      I created this calculated field, but obviously is using 9.90 as "Total_Manufacturer"... I'm looking for result to be 0.109


      I have been reading a lot and I think a LOD would help, but I haven't been able to implement it since it says that LOD can not use table calculations.


      Any idea how to achieve this?? I really appreciate it... I'm attaching package workbook