Calculated values are derived metrics stored in Datarails, created by applying formulas to raw data using built-in functions or custom expressions. These values provide insights not directly visible from the raw data and are often calculated manually in Excel.

For example, instead of manually calculating Net Income by subtracting expenses from revenue, you can create a calculated value called 'Net Income' to automate this process.

Calculated values can be utilized in Reports, the Report and Budget Builder, and Dashboards.

Existing Calculated Values

Datarails environment includes over 20 pre-built calculated values for the 'Financials' table. To review the full list, navigate to the 'Tables' section and select the 'Financials' table. Then, click the three dots ellipsis icon and choose 'Calculated values.' Some of the available calculated values include:

• Net Income 
• Net Income %
• Gross Profit
• Gross Profit %
• Operating Profit
• EBITDA
• EBIT
• EBIT %
• Current Ratio
• Quick Ratio
• Compensation Expense

Create a Calculated Value

To use a calculated value, you first need to create it or ensure it exists. Follow these steps to review existing calculated values or create a new one:

1. Access the 'Tables' section from the left-side panel.
2. Choose the table where you want to add a calculated value.
3. Either click the three dots ellipsis icon and select 'Calculated values' or click the table name. In the Table fields window, click the three dots ellipsis icon and choose 'Calculated values.'
4. The 'Calculated values' window will open, displaying a list of all existing calculated values. You can edit these using the pencil icon.
5. To add a new one, click the 'Add New' button.
6. In the new window, assign a name to the calculated value.
7. In the Formula bar, insert the Formula. You can use operators like +, -, *, /, change the format to a percentage, adjust the number of decimals, and choose fields to use in your formula from the 'From' section.
8. For example, to create a 'Gross Profit' calculated value, type 'Gross Profit' in the Name section. Search for the 'Account Group L2' field and double-click 'Income' and 'Cost of Goods Sold,' adding a '-' between the two fields. You can also adjust the format to create 'Gross Profit %'.
9. Click the 'Save' button. 

Use Calculated Values

To use calculated values in the Excel add-in, Report Builder, or Budget Builder, you first need to create a function that represents the calculated value. This function allows you to incorporate the calculated value into reports. You can create this function from either the Excel add-in or directly from the Datarails environment:

1. 1. Create from Excel add-in: open an Excel file and ensure you are connected to your environment via the Excel add-in. Click on Functions and choose 'Add function'. A new function window will pop up. Continue to step 2.
      Create from the environment: Navigate to the Excel add-in (functions) section in the left panel. Click on the plus icon. A new function window will pop up. Continue to step 2.
   2. In the New Function window, select the table you'd like to use. Search for the field you want to use as a value and drag it to the value section. Apply any necessary filters using the Filters section and choose the 'Default Aggregation Field.'
   3. Next to the value you selected, click the calculator icon to open the list of calculated values.
   4. Choose the relevant calculated value by ticking the box and click 'Apply.' The calculator icon will turn blue, indicating that the value now uses a calculated value.
   5. Click 'Save.

Use Calculated Values in Excel add-in

Learn how to create formulas in Excel add-in. 

1. Select the file you want to add the calculated value to and download it.
2. Open the downloaded file and ensure you are connected to your environment via the Excel add-in.
3. In the add-in, click on 'Functions' and choose 'Manage Functions'.
4. A list of functions will pop up. Choose the function you want to use and click on the plus icon next to it.
5. The function will be added to the selected cell. Continue building the DR.GET function by following the instructions in the Creating a formula article. 

Use Calculated Values in the Report and Budget builder

1. Navigate to the Report Builder or Budget Builder and create a new report or budget.
2. In the function section, choose the function you want to use. 
3. Continue building the report or budget by adding rows, columns and filters. Follow the instructions in the Report Builder and Budget Builder articles for detailed guidance. 

Use Calculated Values in Dashboards

1. Open the Dashboards and click 'New'.
2. Click on 'Add widget' and choose 'Custom'.
3. Choose the chart type and click 'Next'.
4. Search for the field you want to use as a value and drag it to the value section.
5. Next to the value you selected, click the calculator icon to open the list of calculated values.
6. Choose the relevant calculated value by ticking the box and click 'Apply.' The calculator icon will turn blue, indicating that the value now uses a calculated value.
7. Continue building your dashboards by adding rows, columns and filters. Learn more about building widgets and dashboards.

Updated July 11, 2024 09:22