The Sales team in our Salesforce org was conducting a performance review, so I created a detailed report to calculate the “Year-to-Date Sales” for each opportunity directly in the report.
In that report, using the row-level formula, I add a row-level formula field that calculates the “Year-to-Date Sales” by checking if the “Close Date” is within the current year and summing the “Opportunity Amount” accordingly.
Here, I will explain the row-level formula in Salesforce reports. Then, we will see how to create row level formula in Salesforce report with an example and a step-by-step explanation.
Row-Level Formula in the Salesforce Report
In a Salesforce report, a row-level formula is a custom formula field (column) that we can create directly within a report to calculate values for individual rows of data.
Unlike summary formulas, which calculate totals or averages across multiple rows, a row-level formula operates independently on each record (row).
Row-level formulas can execute calculations on existing fields in the report, apply logic, and create new calculated values that appear with the other data in the report.
Thus, row-level formulas allow us to add custom metrics and calculations without having to change the base Salesforce objects or data.
Important: We can create a row-level formula only in Salesforce Lightning.
Create Row Level Formula in Salesforce Report
Using the above scenario, we will implement the following steps to understand how to use a row-level formula in the Salesforce report.
- Go to “App Launcher” -> Search “Reports” -> Click on “Reports.”
- Then click on the “New Reports” button to create a new report.
- Go to All category -> select a Report Type, search “Opportunities, “and you can search for the object you want to create a report for. -> Select Opportunities. -> Click on Start Report.
- Then add the necessary fields/columns in the opportunity report, along with the close date and amount field, because we want to find the total amount of sales till the current close date.
- After that, click on Outline. -> Then click the dropdown arrow. -> Select Add Row-Level Formula.
- Then, you will see the formula editor interface. Now, you need to fill in the necessary information.
- Column Name: Enter the label of the column you will create. I have given it “Year to Date Sales.” The description is optional.
- Formula Output Type: Here, you need to select based on which field you are creating the formula. On that basis, you can select the output type. Here, I have selected the Number.
- Formula: In this editor, you must enter the logic and insert the fields you want to calculate. Then, after validating the formula, click the Apply button.
IF (
YEAR ( CLOSE_DATE ) = YEAR ( TODAY() ),
AMOUNT, 0 )This formula checks that if the close date field year is equal to the current year, it will display the amount field value; otherwise, it will return 0.
- Now you can see the new formula column has been displayed at last with a name that we entered in the formula editor, and it shows the amount for the current year.
In this way, we can use the row-level formula in Salesforce to display the row-level calculations.
Row Level Formula For Multiple Field Values in Salesforce Report
Now, we will use a row-level formula to display multiple field values in a single column. In this, we can use only one filter instead of applying different filters to each field.
- To add the row-level formula, follow the steps above to open the formula editor.
- Now you need to fill in the necessary information to define the row-level formula.
- Column Name: Enter the label of the column you will create. I have given it “Combine Fields.”
- Formula Output Type: Here, you need to select based on which field you are creating the formula. On that basis, you can select the output type. Here, I have selected Text.
- Formula: In this editor, you need to enter the logic, insert the fields that you want to combine, and connect them using the Concatenation operator. [TEXT(STAGE_NAME) & ” , ” & TEXT(LEAD_SOURCE) & ” , ” & TEXT(TYPE)]
- Here, I have used the “TEXT” function because picklist values do not support the filter formula directly; for that, I convert them into “TEXT.”
- Then insert the fields you want to combine and use & “,” & to concatenate them.
- Validate: After entering the formula, click the Validate button to check whether it is correct.
- Then click on the Apply button.
- Here, you can see the “Combine Fields” column has been added to the report.
- If you want to search for any value whose fields have been added in the “Combine Column,” then you need to do the following –
- Click on the “Apply Filter” option. -> Select the “Combine Fields” field from which we will filter the values.
- Select the operator “Contains” -> Enter the value that you want to filter. Here, I have entered “Existing Customer – Upgrade.” -> Click on the Apply button.
- Here, you can see all records displayed that contain the Existing Customer – Upgrade text value in the fields. Here, we also don’t need to apply separate filters to different fields and use OR filter logic.
This is how we can create a row-level formula and apply it in the Salesforce report.
Conclusion
I hope you have an idea about row-level formulas in Salesforce reports and that they are different from summary formulas. We have seen two different examples with different approaches to explain how to create a row-level formula in the Salesforce report.
You may like to read:
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.
