# Percent Formula Field using MAX function (for single field)

I am trying to create a custom formula field (Percent) off of another custom formula field (Number) that self references the Number field to find the highest (MAX) value and to convert the Number field/values into Percent.

Current Custom Formula Field (Percent) created:

``````Average_Daily_Eligible_Submissions__c / MAX(Average_Daily_Eligible_Submissions__c)
``````

However, the results seem to indicate the formula isn't calculating as expected. The range of average daily eligible submissions (number) goes from 0 to 1. The record with average daily eligible submissions = 1 should return 100% (which is happening) but record with average daily eligible submissions = 0.7 (as an example) should return 70% as in 0.7/1 (ie. 1 being the max value). However the later returns 100% too currently.

Any idea how to fix this formula/or how to use MAX function appropriately?

THANK YOU SO MUCH!

• I realize that the reason it's returning 100% for all record is because it's looking at the current record's values (rather than retrieving the highest value across multiple records) Jul 8, 2022 at 20:12
• this is a report formula? Jul 8, 2022 at 23:22
• No, it's a formula field I am trying to create on the Contact object. My goal is to convert a Number field (already existing on Contact object) into a % field (where the % is calculated in relation to the highest/max value of the said Number field, across all contact records). I want to know what % each contact record represents against the highest (contact) record/value. Jul 9, 2022 at 21:04
• I'm confused formula fields can't reference multiple records of the same object. Jul 9, 2022 at 21:13
• I am finding this out now ah! I am clearly learning! Do you have any idea how I could store (dynamically) the highest value of a field (across all records of a single object), maybe using flow? If I could store this (max value) number on all individual records (find the max value > update a field on the contact record), I could then create my formula field using the fields of the single record. Not sure if what I am saying is clear enough ah! Jul 9, 2022 at 23:49

If I understand the requirements correctly, you will need to do something like the following:

• Create a scheduled batchable (Apex) that runs daily (perhaps after today's quotes are in). Be sure to implement `Database.Stateful`

• The `start()` method does a query to count the number of quotes received that day, week, month, year, last12 months, etc. Save this in an instance variable of the batchable
• The `start()` method then does a Select SOQL to choose the Contacts that need to be updated with the count(s) fetched above
• The `execute()` method will get 200 Contacts at a time and you can use trivial DML to update the Contacts
• The `finish()` method does nothing.
• With the job, you'll have in every Contact the denominator you will need to then construct a formula field(s) that calculates the tier. Note this solution defines the tier as of the end of the day, not real-time during the day

If you go this route, and get stuck, post a new question with your code rather than keeping this question/answer going

• Thanks for your response! This will be out of my league so I will have to ask for extra support. I appreciate your help though! Jul 26, 2022 at 2:08