Cube Management: Create Calculations and Measures

Overview

Once you’ve loaded your data, configured fields, and set up mappings, you may want to extend your analysis with additional measures. The Calculations tab allows you to create custom metrics inside Diligent so they are always available when building reports in Excel or Power BI.

These calculations are built using DAX-style logic (Data Analysis Expressions), the same language used in Power BI and other tabular models. Most users will use this tab for simple arithmetic-based measures, but more advanced logic is possible with the right expertise.

What the Calculations Tab Does

• Lets you define new measures that extend your cube’s data model.
• All calculations created here show up in your cube as a measure that can be added to the Values section of a pivot table in Excel or Power BI.
• Measures remain tied to the cube, so they refresh automatically with your data.
• You must click Process Cube Data after adding or editing calculations for them to appear in Excel or Power BI.

Common Examples of Calculations

• Net Sales = Gross Sales − COGS
• Gross Profit = Revenue − COGS
• Gross Margin % = Gross Profit ÷ Revenue
• Average Order Value = Revenue ÷ Orders
• Revenue per Customer = Revenue ÷ Customers

How to Add a Calculation

1. Go to the Calculations tab in Cube Management.
2. Click Add calculation
3. Select your base fields (e.g., Revenue, COGS) and define the arithmetic expression.
   • Use the expression builder to apply simple addition, subtraction, multiplication, or division.
4. Enter a clear, descriptive name for your measure (e.g., “Gross Margin %”).
5. Save the calculation.
6. Click Process Cube Data in the upper right to apply the change.
7. Once processing succeeds, open Excel or Power BI and add the new measure to your reports.

📌 Tip: Calculations are not retroactive until processed. Always process the cube after edits.

Best Practices

• Keep it simple. Focus on arithmetic or ratio-based measures.
• Validate inputs. Ensure underlying fields are set correctly in the Configuration tab (e.g., Revenue marked as Numeric).
• Name clearly. Avoid abbreviations—make measure names business-friendly (e.g., “Average Order Value” vs. “AOV”).
• Check results. Compare new measures against expected financial outputs before sharing reports.

Advanced Calculations

• The Advanced tab (visible in your screenshot but currently greyed out for most users) supports custom DAX formulas.
• Most users will not have access to this functionality.
• If you need advanced calculations, such as:
  • IF statements or nested conditions
  • Rolling periods (e.g., rolling 12 months with exceptions)
  • Complex allocations or dependencies across multiple tables
    → Please reach out to Support or your Project Admin.

Key Notes

• The Calculations tab, like all tabs in Cube Management, can be revisited at any time. You do not need to follow the tabs in strict order.
• Process Cube Data is always required for your new measures to flow into Excel or Power BI.
• Bundling changes before processing saves time.

Related Articles

• Cube Management: Configuration and Setting Key Fields
• Cube Management: Mapping and Relationships
• Cube Management: Loading and Managing Data

Adding calculations is an important way to unlock deeper insights from your cube without modifying your source data. By starting with straightforward arithmetic measures, you ensure results are reliable and easy for your team to interpret. If you need more advanced logic, don’t hesitate to involve your Support team or Project Admin. With the right calculations in place, your reports in Excel and Power BI become more powerful and actionable.