Forum Discussion

YordanPetrov2's avatar
YordanPetrov2
Qrew Trainee
5 years ago

Gauge report sum column values

Hello,

I am trying to create a gauge chart where I can find sales/estimates ratio in %. I have 4 columns in the table which are needed, 2 for dynamic filters - person assigned and period (no issue with those). The other 2 fields are project estimate price and project sale price, both are currency fields. I would like to create a report formula that sums all the column values from project sale price, then sum all the column values for total estimate price and finally divide sum(total sales price)/sum(total estimate price). This ratio will be shown on a gauge chart and through the dymanic filters management can slice the data for employees and period.

Any ideas how to achieve this will be greatly appreciated!

------------------------------
Yordan Petrov
------------------------------
Relationships
reports and charts
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    5 years ago
    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/
    ------------------------------