3 Replies Latest reply: Feb 22, 2016 4:36 PM by Stefan Wühl RSS

    Summary Table with numerous dimensions compared

    Crystle Stamper

      I need to create a summary table to compare Customers to their respective regions, markets and overall by the potential of what each customer has purchased in the current fiscal year.

       

      Here's an example that might explain it better.

       

      CustomerPotential by MarketPotential by RegionPotential Overall
      ABC602050
      DEF202030
      TOTAL307060

       

      So that is how I would like my finished table to look.

       

      The problem is with calculating the potential.

       

      The way they want the potential calculated is to ONLY count the potential if the Region/Market/Overall average of sales per commodity is greater than the specific customer average of sales per Commodity. I already have this table set up in one of the tabs, but it does not summarize and it set to calculate based on the selection of the Customer. See example below.

       

      With Customer ABC selected

       

      CommodityCustomer AvgMarket AvgMarket PotentialRegion AvgRegion PotentialOverall AvgOverall Potential
      Apples10201040206050
      Oranges308050100200
      Bananas50400200100
      TOTAL602050

       

       

      As you can see, the totals from this chart's potential, matches the summary chart. That is basically what I need to do. But every time I try to do a formula for it, it just sums the total of everything minus the total of everything and then gives me 0 for most things if the total customer avg sales is greater than the total avg sales for region or market or overall.

       

      I have tried using Aggr() functions but it doesn't want to exclude the negative values like I need it too.

       

      I've even considered creating the Avgs in a table and putting the values in a field that I can work with.

       

      Any help would be greatly appreciated!