Gauge report sum column values

Since the math gets funky when dealing with percentages, you'll need to calculate this value on a table that is a Parent to these Sales. This is the process that I would suggest:

1. Create a general 'Metrics' table and create 1 record on this table
2. Create a relationship from the 'Metrics' table to the 'Sales' table
3. Pull a report on the 'Sales' table that includes the new 'Related Metric' field and, using the 'Search and Replace in this Report' change the value of 'Related Metric' to 1
4. Create relationships from the 'Metrics' table to the tables for 'Employees' and 'Period', making the 'Metrics' table the Child
5. Create Lookup fields from the 'Metrics' table to the 'Sales' table for 'Related Employee' and 'Related Period'
6. On the 'Sales' table, create a Formula Checkbox field called 'Include in Metrics Report' with the following criteria:
   1. ('Lookup Employee'=0 AND 'Lookup Period' =0) OR
   2. ('Lookup Employee'='Related Employee' AND 'Lookup Period' =0) OR
   3. ('Lookup Employee'=0 AND 'Lookup Period' ='Related Period') OR
   4. ('Lookup Employee'='Related Employee' AND 'Lookup Period' ='Related Period')
7. On the 'Metrics' table, create 4 fields:
   1. A summary field for 'Project Estimate', filtered by 'Include in Metrics Report'
   2. A summary field for 'Project Sale', filtered by 'Include in Metrics Report'
   3. A formula field for Sales Ratio, using the previous 2 summary fields to calculate your value
   4. A report link field that uses 'Record ID#' on the 'Metrics' table as both sides of the matching setup
8. On the 'Metrics' table, create your Gauge Report using the 'Sales Ratio' field as the value to measure
9. On the 'Metrics' table, setup your form with your 2 selection fields and an embedded report for the 'Sales' table, selecting your new report as the report to display

Users will use your drop-down fields as dynamic filters and will need to be able to edit the record to see that, but it should work for you.

------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quick Base Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
------------------------------