# Plan No-code, Full-stack Planning + BI + Data Management Suite on Microsoft Fabric **Plan** is an Enterprise and Corporate Performance Management (EPM & CPM) solution built directly into **Microsoft Fabric**. It enables organizations to create, manage, and analyze plans—such as budgets, forecasts, and scenarios—within the same governed platform used for data, analytics, and AI. Plan brings together three integrated capabilities in a single unified suite: 1. **Planning Applications** 2. **PowerTable** for data management and data applications 3. **Intelligence** for integrated reporting and insights By planning natively in Fabric, Plan eliminates the need for separate planning systems and spreadsheet-driven workflows. Organizations can unify **goals, plans**, and **actuals** on shared semantic models, enabling a shift from manual planning to **continuous & autonomous decision-making.** Plan completes a **full circle, future-ready data foundation** by unifying historical data, real-time signals, and forward-looking projections in a single environment that is **AI agent–ready by design**. ### What Plan Enables Plan allows organizations to: * Perform **enterprise planning, scenario modelling, and what-if analysis** * Model and integrate **budgets and forecasts** * Automatically **write planning results back to Fabric SQL** * Unify **goals, plans, and actuals** on a shared semantic model * Manage **forward-looking reference data** * Perform **variance reporting and analysis** * Operate entirely within a **single, governed Fabric environment** ### Why Plan in Fabric Traditional enterprise planning typically relies on multiple tools: * BI platforms for reporting on historical data * Separate planning or CPM tools for forecasts and budgets * Spreadsheets for modelling and scenario analysis This fragmented approach introduces data silos, manual reconciliation, delayed insights, and governance challenges. Plan addresses these challenges by bringing planning directly into Fabric. Historical data, real-time signals, and future projections are combined in one platform, using consistent definitions and governed access. ### Unified Data Foundation Plan is built on Fabric semantic models, ensuring that planning and analytics share the same trusted business logic. With Plan, organizations can combine: * **Historical data** from Fabric analytics * **Real-time** or **refreshed operational data** * **Future projections and assumptions** Because plans are stored and governed in Fabric, they remain aligned with enterprise reporting and downstream analytics. ### Designed for Business Users and the Enterprise Plan is designed to be accessible to business users while meeting enterprise requirements. **For business users:** * Native experience inside Fabric * **No code / low code** interactions * Familiar **Excel-like interface** **For enterprises:** * Centralized governance * Security and access controls * Scalability for large planning models * Alignment with Fabric administration and compliance standards This balance enables finance teams and business stakeholders to participate directly in planning without heavy IT dependency. ### Core Components of Plan Plan consists of four primary components that support end-to-end planning workflows. ### Planning Sheets Planning Sheets are used for budgeting, forecasting, and scenario modelling. They allow users to define assumptions, inputs, and calculated outcomes in a familiar, spreadsheet‑like experience. ### PowerTable Sheets PowerTable Sheets enable structured planning at scale. They support large, dimensional planning models aligned with Fabric data structures and semantic models. ### Intelligence Sheets Intelligence Sheets provide analytical insights and AI‑assisted reasoning over planning data. They help users understand variances, trends, and forward‑looking implications. ### InfoBridge InfoBridge connects and integrates data across systems, ensuring planning data stays aligned with Fabric workloads and source systems. Plan extends Microsoft Fabric beyond analytics into enterprise decision intelligence. By embedding planning directly into Fabric, organizations can unify data, analytics, and planning in a single platform, reducing manual effort, improving alignment, and enabling proactive, AI-assisted decision-making. # Prerequisites for Plan This article lists *all* the required prerequisites, tenant settings, and capacity settings that must be configured to use Plan. ### Tenant settings [Fabric administrators](https://learn.microsoft.com/en-us/fabric/admin/roles) can grant access to these settings in the [admin portal](https://learn.microsoft.com/en-us/fabric/admin/admin-center) under [tenant settings](https://learn.microsoft.com/en-us/fabric/admin/tenant-settings-index). 1. Under [**Integration settings**](https://learn.microsoft.com/en-us/fabric/admin/tenant-settings-index#integration-settings), enable **Allow XMLA endpoints and Analyze in Excel with on-premises semantic models**.
2. Under **Developer settings**, enable [**Embed content in apps**](https://learn.microsoft.com/en-us/fabric/admin/service-admin-portal-developer#embed-content-in-apps)**.** 3. For service principal-based authentication, enable [**Service principals can call Fabric public APIs**](https://learn.microsoft.com/en-us/fabric/admin/service-admin-portal-developer#service-principals-can-call-fabric-public-apis) under **Developer settings**.
### Capacity settings 1. Semantic models used with Plan must be hosted on supported capacities, such as * **Power BI Premium capacities (P1, P2, and higher)** * **Microsoft Fabric capacities (F SKUs)** 2. Plan scenarios that rely on XMLA endpoints and embed tokens require supported **Microsoft Fabric capacities (F SKUs)** or **Power BI Premium capacities (P1–P5)**. Power BI Pro and Power BI Premium Per User (PPU) aren't supported for these scenarios. Some lower-capacity SKUs can also have XMLA and memory limitations that prevent supported usage. 3. In the Power BI Admin portal, under **Capacity settings**, ensure that the **XMLA Endpoint** setting is configured as **Read Only** or **Read Write**.
### Semantic model connection owner permissions The shared cloud connection owner, whether a user account or service principal, must have a workspace **Member** or **Admin** role. ### Database connections #### Optional requirements These are optional database connections that you can configure for specific scenarios such as collaboration and writeback. 1. Create and share a Fabric SQL database connection with report viewers so they can collaborate on the plan report. To know more, see [Create a database connection for collaboration](/documentation/readme/create-a-database-connection-for-collaboration). 2. Create and configure writeback destinations if you want to write back the plan data. See [Configure writeback destinations](/documentation/readme/planning-sheets/how-tos/persist-planning-data-using-writeback/configure-writeback-destinations) for more information. {% hint style="info" %} ### Note During Plan item creation, a Fabric SQL database is automatically created in your workspace. This database stores your plan report's metadata. {% endhint %} # Create and share a cloud connection for a semantic model This article explains how to connect to a semantic model from a plan item. To connect a plan to a semantic model, a workspace **admin** or **member** must create a **shareable cloud connection**. Other users can use this connection to access the semantic model. The following steps describe how to create and share the connection. ### Prerequisites Before you can create the semantic model connection, make sure you have the following prerequisites in place: * Data in a [Power BI semantic model](https://learn.microsoft.com/en-us/fabric/data-warehouse/semantic-models) ### Create a semantic model connection This connection is used to connect to your semantic models when you create a plan. The following steps must be completed by a workspace admin or member who has access to the semantic model. 1. In your Fabric toolbar, select the **Settings** icon. Select **Manage connections and gateways > New**.
2. Select **Cloud** as the **new connection**. 3. Enter a **Connection name**. 4. For **Connection type**, select *Power BI Semantic Model*. 5. Select an **Authentication method** * **OAuth 2.0** – Select **Edit credentials**, and then sign in with your Microsoft account. * **Service principal** – Enter the **Tenant ID**, **Service Principal ID**, and **Service Principal Key**. For service principal-based authentication, ensure you enable [**Service principals can call Fabric public APIs**](https://learn.microsoft.com/en-us/fabric/admin/service-admin-portal-developer#service-principals-can-call-fabric-public-apis) under Developer settings in the admin portal.
6. Select **Create.**
### Share the semantic model connection 1. Next to the name of the semantic model connection in your Fabric workspace, select **...** and **Manage users**.
2. Search the **name or email ID** of the users to share the semantic model connection. 3. Set the **access permission** as User, User with resharing, or Owner. 4. Select **Share** to share the connection.
5. The semantic connection created is shared, which can be accessed by other users. Other users can now use this shared connection to connect to the semantic model. ### Connection types supported in Planning sheet Planning supports the following connection types: | Connection type | Support status | Requirements | | --------------- | -------------------------- | --------------------------------------------------------------------- | | Import mode | Supported fully | | | Direct Lake | Supported with limitations | Gateway must use fixed credentials (SSO isn't supported at this time) | | DirectQuery | Supported with limitations | Gateway must use fixed credentials (SSO isn't supported at this time) | ### Connect to a Direct Lake semantic model If you want to connect to a Direct Lake semantic model, follow the steps below. These steps should be performed by workspace **Admin** or **Member** users. 1. Next to the name of the semantic model in your Fabric workspace, select **... > Settings > Gateway & Cloud Connections**.
2. By default, the connection is set to **Single Sign On.** You can create and use a new connection. 3. Select **Create a connection** from the connection list.
4. Enter the new **connection name.** 5. Select an **Authentication method** * **OAuth 2.0** – Select **Edit credentials**, and then sign in with your Microsoft account. * **Service principal** – Enter the **Tenant ID**, **Service Principal ID**, and **Service Principal Key**. For service principal-based authentication, ensure you enable [**Service principals can call Fabric public APIs**](https://learn.microsoft.com/en-us/fabric/admin/service-admin-portal-developer#service-principals-can-call-fabric-public-apis) under Developer settings in the admin portal.
4. Select **create**.
6. Select the newly created direct lake semantic model connection from the list and **apply.**
#### Recommended configuration for Continuous Integration and Continuous Deployment (CI/CD) For an optimal Continuous Integration (CI) and Continuous Deployment (CD) experience, consider the following recommendations: * Create the semantic model connection using **Service Principal** authentication. * Grant the service principal the required access across all environments. This allows the same semantic model connection to be reused across environments during deployment, simplifying CI/CD workflows. {% hint style="info" %} #### Note For service principal-based authentication, enable [**Service principals can call Fabric public APIs**](https://learn.microsoft.com/en-us/fabric/admin/service-admin-portal-developer#service-principals-can-call-fabric-public-apis) under Developer settings in the admin portal. {% endhint %} ### Next steps Now that your semantic model connection is created, you can create a Planning sheet that uses this connection: Create a [Planning sheet](/documentation/readme/creating-a-planning-sheet). ### Related content [Prerequisites for Plan](/documentation/readme/prerequisites-for-plan) [Trobleshooting guide](/documentation/readme/planning-sheets/troubleshoot) # Create a database connection for collaboration This article explains how to connect to a database for collaboration from a Plan item. The connection is optional and allows viewers to collaborate on the plan. ### Prerequisite You have access to Planning sheets. ### Create a connection to a database for collaboration 1. Select the **Set up connection.**
2. Select **Create Connection** to create a Fabric SQL Connection or select an existing connection.
3. Select **Create new connection** from the Connection credentials dropdown.
4. Enter a **Connection name**. 5. Select **Authentication kind** as **Organizational account.**
6. Select **Create.** A database connection is created to store collaboration data. ### Share the database connection You can share the created connection and manage the user permissions. Click **Manage users**. 1. Next to the name of the connection in your Fabric workspace, select **...** and **Manage users**.
2. Edit the permissions and share as needed.
# Creating a Planning Sheet This article describes how to get started with your first Planning sheet in plan. ### Prerequisites Before you set up Planning sheets, make sure you have the following prerequisites: * Data in a [Power BI semantic model](https://learn.microsoft.com/en-us/fabric/data-warehouse/semantic-models), and a [connection to your database](/documentation/readme/planning-sheets/how-tos/create-a-database-connection). * Data in a [Fabric SQL database](https://learn.microsoft.com/en-us/fabric/database/sql/overview), and a [connection to your semantic model](/documentation/readme/create-and-share-a-cloud-connection-for-a-semantic-model). {% hint style="info" %} Plan in Fabric IQ is now available to organizations worldwide as part of the Microsoft Fabric SKU. New billing meters have also been introduced and are now available for billing. {% endhint %} {% hint style="info" %} ### Note You can find the required tenant and capacity settings, as well as all other prerequisites, here: [Prerequisites for Plan](/documentation/readme/prerequisites-for-plan). {% endhint %} ### Create plan item 1. Start in your Fabric workspace. 2. Create a **New item > Plan**.
3. Name your plan and create it.
During planning item creation, a Fabric SQL database is automatically created in your workspace. This database stores your plan report's metadata. You can create a database connection for collaboration. This step is optional and is required only if you want to collaborate. For more information, see [Create a database connection for collaboration](https://app.gitbook.com/o/Bi5mNLq31yHE9Ep9vISb/s/Utolck8kt8atqxFPsEBn/~/edit/~/changes/153/planning-sheets/how-tos/create-a-database-connection-for-colloboration). ### Create a Planning sheet 1. In your new plan item, you see options to get your data from the semantic model or Excel/CSV and create a Planning sheet from it, or to start with a Planning sheet and then connect it to data.
2. Select **Planning**, **Name** the new Planning sheet, and **Create** it.
### Connect Planning sheet to a semantic model connection 1. In your new Planning sheet, select **Add.** 2. Connect to your Fabric SQL connection under **Select a Connection.**
3. Select the Semantic Model and select **Add**.
4. Add semantic model data into your fields. Your first Planning sheet is now created.
# Known Limitations in Plan Review the following known issues and limitations before you begin working with plan. Supported limits might vary depending on client resources, Fabric capacity, and Power BI XMLA query limits. ### B2B user support Plan doesn't support Microsoft Entra B2B IDs. ### Private link support Plan items aren’t supported in workspaces or tenants that use private links. ### Semantic model * You must have *Admin* or *Build* permissions on the semantic model. * Semantic models in Direct Lake mode require additional configuration. * Semantic model connections support only OAuth-based and service principal–based authentication. * Semantic models published in *My workspace* aren't supported. ### Semantic model renaming Don't rename a semantic model that's connected to a Plan item. Renaming the semantic model breaks the connection, and the Plan item no longer works with the renamed semantic model. ### Capacities supported Power BI Pro and Power BI Premium Per User (PPU) aren't supported for Plan scenarios that use XMLA endpoints and embed tokens. Similarly, lower-capacity SKUs that do not support XMLA endpoints are also unsupported. ### Database-level row-level security (RLS) support PowerTable doesn't support user-specific database-level row-level security (RLS) when connecting to Fabric SQL tables through a database connection. As a result, users might see rows that differ from the expected RLS-filtered results. This limitation exists because PowerTable executes all database queries by using the identity associated with the database connection that the user configures during sheet creation, rather than the identity of the signed-in PowerTable user. ### PowerTable DMTS connection recovery If you delete the DMTS connection that you configured for a PowerTable sheet, or if it becomes unavailable, you can't open the sheet to update the connection. The connection recovery screen doesn't appear, and you see the message "DMTS connection is deleted or not found." To recover, create a new PowerTable sheet by using the Existing Table option and configure the same table again. ### Workspace permissions * Users with the *Contributor* role can't create or share cloud connections. * Users with lower-level workspace roles, such as *Contributor*, can't create Plan artifacts that require embed token generation. ### CI/CD service principal support Automatic application database creation isn't supported when deploying plan items through CI/CD by using a service principal. ### Workspace renaming Don't rename a workspace that contains a Plan item. Renaming the workspace breaks the Plan item, and the item no longer opens. ### Bulk data input limit Bulk data input supports a maximum of 1 million rows. Uploading more than 1 million rows from an Excel or CSV file isn't supported and might cause the upload to fail. ### Maximum number of sheets per item A Plan item supports up to 25 sheets. Keep the number of sheets within this limit to avoid problems when working with the item. ### Maximum number of visuals per item A Plan item supports up to 50 visuals. Keep the number of visuals within this limit to avoid problems when working with the item. ### Infobridge cell limit Each Infobridge query in a Planning sheet supports up to 1.2 million cells. Queries that exceed this limit might fail to load or process. To work with larger datasets, split the data across multiple Planning sheets and append the queries. This approach supports a consolidated workbook of up to about 5 million cells (for example, across five Planning sheets). ### Writeback cell limit Writeback supports up to 1.2 million cells per operation. Writeback operations that exceed this limit aren't supported and might fail. # Row-level security (RLS) behavior in Plan Row-level security (RLS) restricts data access by filtering rows based on the user’s identity. In Fabric Plan, RLS ensures that users can view and interact only with the data they are authorized to access. RLS is enforced at query time, and only the permitted rows are returned to the user. This article explains how behavior varies based on workspace roles and Row-Level Security (RLS) in Fabric Plan ### How RLS works in Fabric Plan RLS in Fabric Plan is inherited from the underlying semantic model or data source. * Filters data based on user context. * Applies at query time for every interaction. * Ensures consistent enforcement across views and calculations. RLS rules are evaluated whenever a user queries data, and only the allowed rows are returned. ### RLS behavior in Plan Fabric Planning connects to semantic models using **embedded tokens**. This authentication mechanism differs from the standard Power BI service approach and can result in different data access and security behavior, particularly when Row-Level Security (RLS) or Role-Based Access Control (RBAC) is configured on the underlying semantic model. #### RLS behavior in Plan compared to Power BI #### No RLS configured * All data is visible. * Behavior matches Power BI. #### RLS configured with role assignment * Data is filtered based on assigned roles. * Behavior matches Power BI. #### RLS configured without role assignment * In Power BI, all data is visible. * In Planning, users see the **union of all role-based data.**
{% hint style="info" %} Planning requires at least one role when RLS is enabled.\ If some data is not included in any role, it will not be visible. {% endhint %} ### Key differences from Power BI * RLS is always enforced in planning. * Users without assigned roles see combined role data, not full dataset access. * Data that is not included in any role is not visible ### Design considerations * Ensure RLS roles cover the full dataset. * Use consistent role definitions to avoid missing data. * Plan for differences in behavior when users are not assigned roles. *** # Roles in Fabric Plan Planning roles provide a flexible, least-privilege access model for planning items. Instead of assigning fixed permissions, plan automatically adjusts your role based on the actions you perform. With dynamic role assignment, you start with the minimum required access and gain more capabilities only when necessary. Plan supports three roles: * *Viewer*: Has read-only access to consume and analyze plans, reference data, and dashboards. Viewers can explore data, filter information, and compare scenarios without modifying planning data or structures. This role is intended for executives and business users who consume plans, dashboards, and forecasts. * *Stakeholder*: Can collaborate on plans by entering data and writing back values. Stakeholders can't permanently modify the structure of planning sheets. While they can temporarily customize layouts for analysis, plan doesn't persist these changes, and other users can't see them. This role is intended for business leads who enter data, validate assumptions, and approve plans. Stakeholders can also create/edit data apps and advanced reports. * *Planner*: Acts as an author and modeler with administrative privileges and can create planning input structures. Planners can manage planning structures, configure business rules, manage writeback destinations, create forecasts and scenarios, and perform advanced planning operations. Planners can create master data, reports, and dashboards. This role is intended for FP\&A teams and analysts who design models, run scenarios, and orchestrate the planning cycle. {% hint style="info" %} Post August 11, Stakeholder users will be able to perform the following operations without being upgraded to the Planner persona: * Creating or editing **PowerTable** sheets * Creating or editing **Intelligence** sheets These operations will continue to be considered Stakeholder persona activities and will not trigger a Planner persona upgrade. The Planner role is not applicable if the pln item contains only an Intelligence or PowerTable sheet. {% endhint %} Role permission matrix:
WorkloadViewerStakeholderPlanner
Planning Budgets, forecasts, scenarios, allocationsReadContributeCreate
PowerTable Reference and master data managementReadCreateCreate
Intelligence Reports, dashboards, and analysisReadCreateCreate
Roles are flexible, and plan assigns them dynamically through time-bound sessions based on user actions. Roles adapt in real time based on how users contribute, without manual role reassignment. ### Relationship between Fabric workspace roles and planning roles Fabric workspace roles and planning roles are independent and serve different purposes. Fabric workspace roles determine your ability to access and manage workspace items. Planning roles determine the actions you can perform within a planning item. {% hint style="info" %} As a best practice, also assign the Fabric workspace Viewer role to users with the Viewer or Stakeholder role in plan. {% endhint %} Recommended Fabric workspace role mapping: | Planning persona | Fabric workspace role | | ---------------- | ------------------------------ | | Viewer | Viewer | | Stakeholder | Viewer, contributor, or member | | Planner | Admin, member, or contributor | This recommendation helps ensure that: * Fabric enforces Row-Level Security (RLS) and semantic model security rules correctly. * Users see only the data they have permission to access. * Stakeholders and Viewers can't enter report edit mode. * Planning templates and report structures stay safe from unintended modifications. ### Dynamic role assignment Plan assigns planning roles dynamically based on user activity. Users typically begin in a Viewer session. As users perform actions that require extra privileges, plan automatically upgrades them to the appropriate role. Examples: | User action | Resulting role | | ----------------------------------------------------------------------- | -------------- | | Open and view a planning sheet | Viewer | | Enter data, write back values, participate in approvals, or collaborate | Stakeholder | | Edit planning items or perform authoring operations | Planner | With this dynamic model, administrators don't need to manually manage role assignments. ### Upgrade roles Upgrade your planning role by performing an action that requires Planner or Stakeholder permissions, or upgrade the role manually. #### Check current role The planning toolbar displays your assigned role. Select the role indicator to display additional information, including current session type, session expiration details, and capabilities of the current role.
#### Role sessions Planning roles operate through time-bound sessions. Plan creates a session when you perform a planning action, such as opening a planning sheet. Each session remains active for 30 days. When you perform an action that requires a higher privilege level, plan automatically creates a new session for the upgraded role. Role sessions help organizations implement least-privilege access while letting users transition between planning responsibilities. #### Upgrade prompts Administrators can control whether plan prompts users before a role upgrade occurs. To display upgrade notifications, in **Workspace settings**, go to **Plan**, and enable **Prompt on Session Upgrade**. {% hint style="info" %} Creating a new plan workload automatically upgrades your session to Planner. Since workload creation requires Planner capabilities, no warning or confirmation prompt appears. {% endhint %} * **Prompt enabled**: When enabled, users receive a notification before plan upgrades the role and can choose whether to proceed. * **Prompt disabled**: When disabled, role upgrades occur automatically when you perform a qualifying action. Upgrade prompts are disabled by default. #### Role lifecycle 1. **Role upgrades:** Plan assigns roles dynamically based on user actions through time‑bound sessions. Role upgrades occur when you perform valid plan actions. You can upgrade roles only to a higher privilege level: * A Viewer can be upgraded to a Stakeholder. * A Stakeholder can be upgraded to a Planner. 2. **Role downgrades:** Plan doesn't support manual downgrades within an active session. 3. **Session expiry:** Each session automatically expires after 30 days. After the 30-day session expires, a new session begins only when the user performs a new action on a plan item. The persona for the new session is determined based on the first successful action performed: * If you only open and view a plan item, the new session starts as a Viewer session. * If you perform a Planner-level action (for example, creating a new plan item or entering edit mode on a valid item), the new session starts as a Planner session. Each new session inherits its role from your first successful activity. ### Fabric planning roles #### Formatting and layout
CapabilityPlannerStakeholderViewer
Change the layout.✅✅✅
Sort, search, filter, rank, and bookmark planning sheets.✅✅✅
Enable totals and subtotals.✅✅✅
Number formatting - convert to percentage, change scaling, adjust decimal places.✅✅✅
Change the font style.✅✅✅
Change value alignment in cells.✅✅✅
Enable ruler✅✅✅
Configure conditional formatting.✅
Apply semantic formatting.✅
Undo/redo and reset formats, values, notes, header order, and row order.✅
Pivot data.✅✅
Add language translations.✅
Add page breaks and enable row hilighlights, gridlines, and table outline.✅
#### Data input, forecasting, and what-if analysis
CapabilityPlannerStakeholderViewer
Insert rows.✅✅
Insert calculated and data input columns.✅
Enter values and distribute them to lower levels in the dimensional hierarchy.✅✅
Bulk edit values.✅✅
Extend time for data input fields.✅
Create and manage forecasts.✅
Close periods, reforecast, and distribute deficits.✅
Insert simulation measures.✅✅
Create scenarios, update settings, copy to base, bulk edit, select input method, pivot.✅✅
Compare scenarios.✅✅✅
Use Optimizer.✅✅
Use model builder.✅
Create locking, distribution, and min/max rules.✅
#### Writeback and export
CapabilityPlannerStakeholderViewer
Export plans to Excel or PDF files.✅✅
Add and manage destinations.✅
Write back and save planning data.✅✅
Enable auto-writeback.✅
Select the writeback type, create writeback filters, and rename columns.✅
View Writeback logs.✅✅
Export Writeback logs.✅
Writeback scenarios and view logs.✅✅
Add destination to writeback scenarios.✅
#### Commenting and collaboration
CapabilityPlannerStakeholderViewer
Add notes.✅✅
Add and assign comments, tag users, and enable the comments column.✅✅
Add report-level comments.✅✅
Edit comments settings.✅
Enable the comments pane to view all comments.✅✅
#### Build planning models
CapabilityPlannerStakeholderViewer
Connect the planning workspace directly to enterprise semantic Models in Power BI/Fabric.✅
Browse the organizational semantic model catalog(metadata) natively within the planning interface.✅
Create Planning, PowerTable, and Intelligence sheets.✅
Visualize planning sheets with Intelligence.✅
Import and save data from internal sources such as Planning and PowerTable sheets, as well as external sources such as CSV, Excel, JSON, etc.✅✅
#### PowerTable {% hint style="info" %} For plan items that contain only PowerTable sheets, only the Stakeholder and Viewer roles are available. {% endhint %}
CapabilityStakeholderViewer
Browse reference data and PowerTable grids.✅✅
Build/edit no-code reference data apps.✅
Integrate multi-level approval workflows.✅
Configure event-driven automation.✅
Control row & column access permissions.✅
Integrate with plan & intelligence.✅
Participate in approval workflows.✅
Fill data collection forms.✅
Update status and contribute project/time entries.✅
#### Intelligence {% hint style="info" %} For plan items that contain only Intelligence sheets, only the Stakeholder and Viewer roles are available. {% endhint %}
CapabilityStakeholderViewer
View Intelligence Sheets in read-only mode.✅✅
Build/Edit dashboards & reports.✅
Perform ad-hoc analysis.✅
Use 100+ chart types in dashboards.✅
Run plan vs. actual variances.✅
Use annotations.✅
Filter data.✅
Apply bookmarks.✅
### FAQs **Q:** **Can you share roles across capacities?** **A:** No. Each capacity evaluates roles independently. **Q: Can you downgrade roles?** **A:** No, plan doesn't support downgrades. You can only upgrade roles to higher privilege levels; however, your assigned role automatically expires after 30 days. **Q: What happens when my role session expires?** **A**: The next time you interact with a planning item, plan creates a new session. Your first successful action determines the role for the new session. **Q: Do planning roles affect Fabric workspace permissions?** **A**: No. Planning roles and Fabric workspace roles are independent security models that Fabric evaluates separately. # Disaster recovery for Plan Experience-Specific Recovery Procedures for Planning in Fabric IQ This guide walks you through the recovery procedures for the plan experience in IQ. It covers plan templates & app definitions, writeback data, approval workflows & cell-level comments, source data, and semantic models. ### Git integration to restore plan items The preferred approach is to synchronize all plan items with an Azure DevOps (ADO) or GitHub repository using Fabric Git integration. After failover, the repository is used to rebuild items in the new workspace. The diagram illustrates this workflow.
Pre-disaster (proactive steps): 1\. In workspace W1, go to **Workspace Settings** and configure Git integration. 2\. Select **Connect and sync** with your ADO or GitHub repository. 3\. Select the plan item(s) to upload to the repository and select **Commit**.
4\. Confirm that the **Git status** of plan items is *Synced*. 5\. Establish a commit discipline - commit after every significant change to a plan definition so the repo always reflects the latest state. Post-disaster (recovery steps): 1\. Create a new workspace W2 inside capacity C2 in the healthy region. 2\. In W2, go to **Workspace Settings** and reconnect to the same ADO/GitHub repository. 3\. Select **Source Control**. Select the relevant repository branch and select **Update All**. All plan items are downloaded to W2. {% hint style="info" %} Only the planning sheet structure and setting are recovered from Git integration. Data input values, notes, and comments entered in the planning sheet will not be automatically restored. It requires Fabric SQL restore. Semantic model data also needs to be recovered separately. {% endhint %} The following components are restored after recovery: * **PowerTable sheets:** Source table settings, column configuration, row access, visual properties (layout, formats, etc.), row identification, comment settings, slowly changing dimensions (SCD), approvals, automations, and forms. * **Planning sheets:** Sheet properties (formatting, conditional formatting, etc.), comment settings, writeback settings, data input columns, data input rows, scenarios, and bookmarks. * **Infobridge:** Infobridge sources, infobridge queries, transformation steps, writeback destinations, writeback settings, linked query mappings, query groups, visual properties (blend). These items can't be recovered: file-based sources (CSV, Excel), cross-workload sheets that use file-based sources. * **Intelligence:** All charts and matrices, insert sheets. ### Fabric SQL restore Data inputs and comments in planning sheets, tables used in powertable, and writeback data are stored in SQL databases and must be considered as part of your disaster recovery strategy. * **Restore plan metadata** Each plan item is associated with a \_\_fabric\_plan\_sys database that stores metadata for planning features, including comments, scenarios, data inputs, and writeback configuration. The \_\_fabric\_plan\_sys database is not restored automatically and must be explicitly recovered. * **Restore writeback databases** If your plan uses SQL writeback destinations, the associated databases must also be recovered manually. Configured SQL writeback destinations are not restored automatically. * **Restore tables used in powertable** Any tables created using PowerTable are stored in a Fabric SQL database. These tables must also be recovered during DR. To recover SQL databases, see the [SQL database](https://learn.microsoft.com/en-us/fabric/security/experience-specific-guidance#sql-database) section. ### Related Documentation * [Microsoft Fabric disaster recovery guide](https://learn.microsoft.com/en-us/fabric/security/disaster-recovery-guide) * [Fabric experience-specific DR guide](https://learn.microsoft.com/en-us/fabric/security/experience-specific-guidance) * [Fabric SQL SqlPackage reference](https://learn.microsoft.com/en-us/fabric/database/sql/sqlpackage) * [OneLake disaster recovery](https://learn.microsoft.com/en-us/fabric/onelake/onelake-disaster-recovery) * [Fabric Git integration](https://learn.microsoft.com/en-us/fabric/cicd/git-integration/intro-to-git-integration) * [Integrate OneLake with Azure storage explorer](https://learn.microsoft.com/en-us/fabric/onelake/onelake-azure-storage-explorer) * [Fabric Plan documentation](https://learn.microsoft.com/en-us/fabric/iq/plan/overview) # Planning sheets The Planning sheets component of Plan enables organizations to implement structured, collaborative, and data-driven planning processes within their enterprise data environment. A Planning sheet is a structured workspace in Plan that allows users to enter, update, and analyze planning data across defined business dimensions such as time, department, account, or product. Planning sheets provide a controlled environment for budgeting, forecasting, and scenario analysis while ensuring that planning data follows organizational rules and governance policies. The platform is designed for business users and features a no-code, self-service architecture. #### Why use Planning sheets? Use Planning sheets to deliver **strategic business value** by helping your organization do the following: * Improve forecast accuracy through data-driven planning * Accelerate planning cycles with streamlined workflows * Align cross-functional teams on shared plans and targets * Maintain controlled governance and approval processes * Reduce risks associated with manual spreadsheets Planning sheets also provide **measurable operational outcomes**, including: * Shortened budget and planning cycles * Increased reliability of forecasts * Reduced effort required for data reconciliation * Improved auditability and compliance of planning processes. #### **Where to use Planning sheets** Plans are used in several areas of the platform: * **Budgeting**: Define annual or quarterly budgets. * **Forecasting**: Update projections based on current performance. * **Financial planning**: Model revenue, expenses, and profitability. * **Operational planning**: Plan metrics such as sales targets or headcount. * **Reporting and analysis**: Compare plan data with actual results in dashboards and reports. #### Key capabilities The following table lists the core capabilities of Planning sheets. | Capability | Description | Key features | | ----------------------------------- | ------------------------------------------------------------------------------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Data exploration and analysis** | Enables users to interactively explore planning data to identify trends, patterns, and anomalies. | - Filtering and sorting data - Top N ranking - Hierarchy navigation - Grouping rows and columns - Adjustable column widths and layouts | | **Planning and forecasting** | Supports enterprise planning workflows including budgeting, forecasting, and scenario analysis. | - Budget creation - Forecast management - Scenario analysis - What-if simulations - Rolling forecasts - Version management and snapshots | | **Business logic and calculations** | Allows users to define formulas, calculations, and custom logic directly in planning sheets. | - Insert data input rows - Add template rows across hierarchy levels - Create formula rows using Excel-style expressions - Insert manual input columns - Create calculated columns and measures - Apply quick formulas for common calculations | | **Data integration (InfoBridge)** | Integrates data from multiple sources to support unified planning and analytics workflows. | - Data consolidation from multiple sources - Data transformations (merge, append, pivot, group) - Real-time data integration - Data mapping between planning sheets | | **Collaboration and comments** | Enables team collaboration and review directly within the planning interface. | - Notes and annotations - Comment threads - `@mentions`- Email notifications - Comment digests | | **Write-back and data storage** | Allows planning inputs to be written back to external systems for persistence and integration. | - Multiple destination support - Write-back logs and monitoring - Secure data persistence - Integration with enterprise data platforms | | **Approval workflows** | Provides governance for planning cycles through structured approval processes. | - Define approval flows - Assign approvers - Review and approve planning changes - Request adjustments | | **Model editor** | Defines and manages the structure of a planning model by configuring dimensions, measures, hierarchies, and calculations | - Map model components to data sources - Validate model configuration before deployment - Maintain centralized governance of the planning model - Define hierarchies for roll-ups and drill-down analysis | | **Cube** | Organizes and stores planning data in a multidimensional data structure. | - Analyze data across multiple dimensions - Aggregate data across hierarchy levels - Process large volumes of planning data efficiently | # Concepts # Fiscal year setup in plan ### What is a fiscal year? A fiscal calendar organizes reporting periods based on an organization's financial year instead of the standard January-to-December calendar year. Depending on the organization, the fiscal year might begin in April, July, August, October, or another month. ### Standard calendar and fiscal calendar #### Standard calendar A standard calendar year always begins in January.

Calendar year

Calendar yearQuarterMonths
2025Q1January, February, March
2025Q2April, May, June
2025Q3July, August, September
2025Q4October, November, December
#### Fiscal calendar A fiscal calendar year begins in the month selected by the organization. Quarter boundaries move with that start month. For an April-start fiscal calendar, the fiscal quarters and months are shown in the following table and image.
Fiscal quarterMonths
Q1April, May, June
Q2July, August, September
Q3October, November, December
Q4January, February, March

Fiscal year starting in April

For an August-start fiscal calendar:
Fiscal quarterMonths
Q1August, September, October
Q2November, December, January
Q3February, March, April
Q4May, June, July
In Plan, you can set any month as the fiscal year start month. {% hint style="info" %} ### Important Plan does not create or manage the fiscal calendar. You must set up a fiscal calendar table that includes fiscal period fields such as Year, Quarter, Month in your semantic model, and then configure the planning sheet or matrix to use those fields. {% endhint %} You can use fiscal calendars across the following time-intelligence features: * Forecast measures * Time extension and future periods * Open and closed periods * Time-based planning ### Setting up fiscal date calendar in plan By setting up fiscal date calendars in plan, you can organize planning and reporting around your financial year instead of the calendar year. For this, you need to create a fiscal date table in your semantic model and map the calendar and fiscal years accordingly. #### Semantic model requirements Before configuring the visual, * Create a fiscal date table in the semantic model that maps each calendar date to the corresponding fiscal periods. * At a minimum, include the fiscal fields required for your reporting granularity, such as *Fiscal Year*, *Fiscal Quarter*, and *Fiscal Month*. * If your organization reports at finer levels of detail, also include: *Fiscal Week* and *Fiscal Day*. The fiscal date table should: * Provide a complete and consistent fiscal mapping. * Include the historical and future dates required for planning and extended forecasting. * Include consistent fiscal labels and corresponding numeric sort fields to ensure fiscal periods are displayed in the correct order on the matrix grid. * Follow your organization's fiscal year naming convention.
{% hint style="info" %} ### Note Plan uses the fiscal fields that you provide in the semantic model. It does not generate, modify, repair or relabel fiscal periods automatically. {% endhint %} #### Add a numeric sort column for sorting requirements Add numeric sort fields for the fiscal display fields. Configure each display field to use its corresponding fiscal-order field in the semantic model so that fiscal periods appear in the correct order. Otherwise, the periods are displayed in alphabetical order by default. For an April-start fiscal calendar, the fiscal months should appear in the following order:
Fiscal month orderDisplay value
1Apr
2May
3Jun
4Jul
5Aug
6Sep
7Oct
8Nov
9Dec
10Jan
11Feb
12Mar
Similarly, sort the fiscal quarters as Q1, Q2, Q3, and Q4 if your sheet includes quarter-level granularity. Incorrect semantic-model sorting can result in unexpected hierarchy order in the sheet.
#### Build the fiscal hierarchy Assign the fiscal fields to the sheet's **Columns** field in order from the highest to the lowest level of granularity: ``` Fiscal Year → Fiscal Quarter → Fiscal Month ``` The recommended hierarchy is: `Fiscal Year → Fiscal Quarter → Fiscal Month`
Depending on the available data and required granularity, supported fiscal paths can also include the following: * `Fiscal Year` * `Fiscal Year → Fiscal Half-Year` * `Fiscal Year → Fiscal Quarter` * `Fiscal Year → Fiscal Month` * `Fiscal Year → Fiscal Week` * `Fiscal Year → Fiscal Month → Fiscal Day` * `Fiscal Year → Fiscal Quarter → Fiscal Month` * `Fiscal Year → Fiscal Quarter → Fiscal Month → Fiscal Day` * `Fiscal Year → Fiscal Week → Fiscal Day` Include `Fiscal Year` whenever the dataset spans multiple fiscal years. Keep the fiscal fields together and order them from the highest to the lowest level of granularity. ### Configure the planning visual Configure these settings before creating forecast measures or extending time periods. 1. After assigning the fiscal hierarchy, go to **Format** > **Appearance** > **Misc.** 2. Configure both these fiscal settings: [**Fiscal Year Start Month**](#fiscal-year-start-month) and [**Fiscal Year Convention**](#fiscal-year-convention).
#### **Fiscal Year Start Month** This option identifies the first month of the organization’s fiscal year. It determines fiscal quarter boundaries and how future fiscal periods are extended. Select the month that exactly matches the fiscal date table. | Business calendar | Select | | ----------------- | ------- | | April–March | April | | July–June | July | | August–July | August | | October–September | October | For example, when *April* is selected: * Q1 is April–June. * Q2 is July–September. * Q3 is October–December. * Q4 is January–March. If the selected month does not match the semantic model, quarters and forecast periods can be interpreted incorrectly. #### Fiscal Year Convention The **Fiscal Year Convention** determines how fiscal year labels map to the calendar year in which the fiscal year begins. Planning supports the following conventions: **Prior** and **Same**. * **Prior**: The fiscal year begins in the calendar year immediately before the fiscal year label. For example, FY 2025 begins in April 2024. The start year is one less than the fiscal-year label.

FY 2025 - Prior convention

For an April-start fiscal year when you use the **Prior** convention: | Fiscal hierarchy value | Actual calendar period | | ---------------------- | ---------------------- | | FY2025 → Q1 → Apr | April 2024 | | FY2025 → Q1 → May | May 2024 | | FY2025 → Q2 → Jul | July 2024 | | FY2025 → Q4 → Jan | January 2025 | | FY2025 → Q4 → Mar | March 2025 | The fiscal label remains 2025 throughout the hierarchy, but the first nine fiscal months fall in calendar year 2024. #### Forecasting under Prior Suppose the visual contains Fiscal Year 2025, representing April 2024 through March 2025. To forecast for the next fiscal year, select the actual calendar range: `April 2025 → March 2026` Planning maps that range to Fiscal Year 2026.
* **Same** – The fiscal year begins in the same calendar year as the fiscal year label. Use **Same** when the organization expects Fiscal Year 2025 to begin in April 2025.
For an April-start fiscal year when you use the **Same** convention: | Fiscal hierarchy value | Actual calendar period | | ---------------------- | ---------------------- | | FY2025 → Q1 → Apr | April 2025 | | FY2025 → Q1 → May | May 2025 | | FY2025 → Q2 → Jul | July 2025 | | FY2025 → Q4 → Jan | January 2026 | | FY2025 → Q4 → Mar | March 2026 | #### Quick comparison For a fiscal year starting in April: | Visual label | Convention | Actual fiscal-year range | | ---------------- | ---------- | ------------------------ | | Fiscal Year 2025 | Prior | April 2024–March 2025 | | Fiscal Year 2025 | Same | April 2025–March 2026 | {% hint style="info" icon="lightbulb" %} ### Tip Identify the first month of a known fiscal year in your source data. If **Fiscal Year 2025** starts in **April 2024**, select **Prior**. If **Fiscal Year 2025** starts in **April 2025**, select **Same**. {% endhint %} ### Plan maps the fiscal dates After these configurations, the fiscal date table connects each actual calendar date to its fiscal reporting period. For example, for a fiscal calendar that starts in April and uses the [**Prior** convention](#prior-convention), the fiscal date fields appear as shown in the following image.
Calendar dateFiscal yearFiscal quarterFiscal month
April 1, 20242025Q1Apr
May 1, 20242025Q1May
June 1, 20242025Q1Jun
July 1, 20242025Q2Jul
January 1, 20252025Q4Jan
March 1, 20252025Q4Mar
The following image shows the updated table after completing all the configurations mentioned so far.
### Create forecast measure with a fiscal calendar 1. Ensure you complete the requirements and configurations: [Semantic model requirements](#semantic-model-requirements), [sorting requirements](#sorting-requirements), [building the hierarchy](#build-the-fiscal-hierarchy), and [configuring the visual](#configure-the-planning-visual). 2. Select **Model** > **Forecast**. 3. Enter the measure name. 4. Select the **Forecast Period** using actual calendar dates. 5. Complete the remaining forecast settings and create the measure.
{% hint style="info" %} ### Important The Forecast Period picker uses calendar periods. Always translate the fiscal period you want to add into its actual start and end dates. {% endhint %} To learn more on creating forecast measures, see [Forecast data](/documentation/readme/planning-sheets/how-tos/forecast-data-to-predict-future-trends). {% hint style="info" %} ### Important Fiscal period labels might not match the corresponding calendar dates. For example, **April in Fiscal Year 2025** might represent **April 2024**, depending on your organization's fiscal calendar. To avoid selecting an incorrect forecast period, use the actual calendar dates instead of the fiscal period labels.\ To learn more, see [Create forecast measure with a fiscal calendar](#create-forecast-measure-with-a-fiscal-calendar). {% endhint %} #### Example: extend an April-start fiscal calendar If the existing FY2027 uses **Prior**, it represents April 2026–March 2027. Select April 2026–March 2027 to create the next fiscal planning year.
After the time extension, the hierarchy contains FY2026 followed by FY2027. The sheet adds forecast periods under the fiscal hierarchy. ### More examples #### April start with 'Prior' | Setting | Value | | ---------------------------- | --------------------- | | Fiscal Year Start Month | April | | Fiscal Year Convention | Prior | | FY2025 actual range | April 2024–March 2025 | | Next complete forecast range | April 2025–March 2026 | | New fiscal label | FY2026 | #### April start with 'Same' | Setting | Value | | ---------------------------- | --------------------- | | Fiscal Year Start Month | April | | Fiscal Year Convention | Same | | FY2025 actual range | April 2025–March 2026 | | Next complete forecast range | April 2026–March 2027 | | New fiscal label | FY2026 | #### August start with 'Prior' | Setting | Value | | ---------------------------- | -------------------------- | | Fiscal Year Start Month | August | | Fiscal Year Convention | Prior | | FY2025 actual range | August 2024–July 2025 | | Q1 | August–October 2024 | | Q2 | November 2024–January 2025 | | Q3 | February–April 2025 | | Q4 | May–July 2025 | | Next complete forecast range | August 2025–July 2026 | #### October start with 'Same' | Setting | Value | | ----------------------- | --------------------------- | | Fiscal Year Start Month | October | | Fiscal Year Convention | Same | | FY2025 actual range | October 2025–September 2026 | | Q1 | October–December 2025 | | Q2 | January–March 2026 | | Q3 | April–June 2026 | | Q4 | July–September 2026 | ### Best practices * Use a single, authoritative fiscal date table. * Include **Fiscal Year** and all lower-level fiscal fields required by the visual. * Include future dates before users create planning periods. * Sort fiscal labels by their corresponding numeric fiscal-order columns. * Use fiscal fields instead of standard calendar fields in the fiscal hierarchy. * Configure the fiscal start month before creating forecast measures. * Validate the **Prior** or **Same** convention against a known date-to-fiscal-year mapping. * Select forecast periods using actual calendar dates instead of fiscal period labels. * Test fiscal year boundaries, especially the transition from Q4 to the following Q1. * Use consistent fiscal settings across all visuals that share the same fiscal calendar. ### Troubleshooting #### Forecast periods appear one year early or late The [**Fiscal Year Convention**](#fiscal-year-convention) is probably incorrect. Compare one known fiscal label and calendar date, then select **Prior** or **Same** accordingly. #### Quarters contain the wrong months Confirm that [**Fiscal Year Start Month**](#fiscal-year-start-month) matches the semantic model’s fiscal calendar. #### Months appear alphabetically Configure Fiscal Month to [sort by a numeric fiscal-month-order](#sorting-requirements) field in the semantic model. #### Planning sheet does not recognize the fiscal hierarchy Verify that: * The visual uses fiscal fields from the fiscal date table. * The fiscal hierarchy is ordered from the highest to the lowest level of granularity. * The fiscal fields use supported time labels consistently. * **Fiscal Year** is included when the data spans multiple fiscal years. #### Forecast extension starts in the wrong month Verify both the fiscal start month and the calendar dates selected in **Forecast Period**. #### Future fiscal periods are missing Confirm that the fiscal date table contains mappings for the required future calendar dates. ### Frequently asked questions (FAQs) #### Can Planning create a fiscal calendar automatically? No. You have to create and maintain the fiscal date mapping in the semantic model before using it for planning. #### Can the fiscal year start in any month? Yes. Select the required month in [Fiscal Year Start Month](#fiscal-year-start-month) and ensure the semantic model uses the same start month. #### What is the difference between Prior and Same? Prior begins the fiscal year one calendar year before its label. Same begins in the calendar year shown by its label. For an April start: * FY2025 + Prior = April 2024–March 2025. * FY2025 + Same = April 2025–March 2026. #### How should I select the 'Forecast Period' while configuring the forecast measures? Select the actual calendar start and end periods to add. Don't select dates based only on the fiscal-year label. #### Can calendar and fiscal fields both exist in the model? Yes. Use the fiscal hierarchy in visuals intended for fiscal planning and the calendar hierarchy in visuals intended for standard calendar reporting. Avoid mixing calendar and fiscal levels within one time hierarchy. #### What happens when the fiscal year starts in August? Q1 becomes August–October, Q2 becomes November–January, Q3 becomes February–April, and Q4 becomes May–July. Prior or Same then determines the calendar year in which that August occurs. #### Are Fiscal Quarter and Fiscal Month always mandatory? Use the levels required by the visual’s granularity. Fiscal Year is essential for distinguishing multiple fiscal years; Quarter and Month are required when users need quarterly or monthly drill-down and forecasting. #### Why does January belong to Q4 in an April-start calendar? Fiscal quarters are counted from the selected start month. With April as month 1, January is fiscal month 10 and therefore belongs to Q4. ### Configuration checklist * [x] A fiscal date table exists in the semantic model. * [x] The fiscal date table includes all required historical and future dates. * [x] The **Fiscal Year**, **Fiscal Quarter**, and **Fiscal Month** mappings match the organization's fiscal calendar. * [x] Fiscal display fields are sorted by their corresponding numeric fiscal-order fields. * [x] The visual uses the fiscal hierarchy from the highest to the lowest level of granularity. * [x] The **Fiscal Year Start Month** setting matches the fiscal date table. * [x] The **Fiscal Year Convention** setting matches a verified fiscal year mapping. * [x] The **Forecast Period** is selected using actual calendar dates. * [x] The fiscal hierarchy and future planning periods have been validated. ### Summary Planning supports fiscal forecasting and time-based planning when the semantic model provides a correctly configured fiscal date table and the visual uses the appropriate fiscal settings. Configure the **Fiscal Year Start Month** and **Fiscal Year Convention** to match your organization's fiscal calendar, and verify the mapping before creating forecast measures. The most important distinction is the meaning of Prior and Same. After the fiscal calendar is configured correctly, select forecast periods using their actual calendar dates. Once that mapping is verified, forecast periods can be selected reliably using their actual calendar dates. # Cube ## What is a cube? In many business scenarios, plans are created separately for each dimension—such as regions, product lines, departments, or time periods—resulting in duplicated effort and fragmented planning. Multi-dimensional cube planning lets you create and allocate plans across multiple dimensions with different granularities in a single step. Cubes enable plans to stay synchronized across different levels of detail. ### Driver-based allocation model Each cube is configured around a [data input measure](https://learn.microsoft.com/en-us/fabric/iq/plan/planning-how-to-input-data) or [forecast](https://learn.microsoft.com/en-us/fabric/iq/plan/planning-forecasting/planning-how-to-build-forecasts) measure. Allocation within the cube is performed using an allocation driver (also referred to as a reference measure or allocation key). The allocation driver is typically a DAX (Data Analysis Expressions) measure from the semantic model, such as prior year actuals, current year revenue, units sold, headcount, or production volume. This driver measure provides the weights and ratios used for proportional distribution. ### How allocation works 1. A value is entered at a summarized level, such as 500 entered for a product without selecting lower-level dimensions (for example, region or province). 2. The selected allocation driver measure determines how the value should be distributed. 3. The value is allocated proportionally across all valid dimension intersections, based on the driver measure’s relative weights. ### Allocation formula (conceptual) The allocated value is calculated by multiplying the entered value by the relative weight of the allocation driver at each valid intersection. The following formula shows how allocations work: ``` Allocated Value = Entered Value × (Driver Value at the intersection ÷ Sum of Driver Values within the hierarchy scope) ``` In the formula, * *Entered Value* is the total value entered at a higher level of aggregation. * *Driver Value at the intersection* is the allocation driver's value for a combination of row and column dimensions. * The *hierarchy scope* includes all valid lower‑level intersections over which the entered value is distributed. ### What allocation means in practice Allocation is performed only for dimension intersections where the driver has a non-null value. Values are distributed based on the relative contribution of each driver value within the hierarchy scope. Allocation respects the dimensional granularity and breakdowns configured in the cube, ensuring consistency with the data model.
### Multidimensional allocation Cubes support distributing plans across: * Dimensions present in the planning sheet * Dimensions not currently visible in the sheet, but configured in the cube breakdown * Multiple granularities, simultaneously Complex enterprise allocations—such as Region > Product Line > Department—can occur in a single action, while maintaining data integrity across the cube. The allocation driver measure doesn't need to be added to the planning sheet. It can exist solely in the semantic model and be used internally as the weighting mechanism. ### Use case: Enterprise level budget allocation Consider an organization allocating an annual budget across Regions, Product Lines, and Departments. The organization can follow these steps to use a cube: 1. Enter the total budget at a higher level. 2. Select a driver measure (for example, prior year actuals) as the allocation driver. 3. The cube proportionally distributes the budget across all valid intersections. 4. Allocations remain synchronized across all dimensions—even the dimensions not visible in the current sheet. This approach avoids manual breakdowns, duplicate models, and reconciliation errors. ### Use case: Multi-granular assumptions with hierarchical allocation Consider an organization planning across two core hierarchies: * Geography Hierarchy: Region > Country * Product Hierarchy: Brand > Category > Product These hierarchies define the full analytical space (Region × Country × Brand × Category × Product × Time). The organization can follow these steps to apply a cube-driven planning model: 1. Capture assumptions at their natural grain. Each assumption is entered at the level most relevant to the business: * Revenue Plan > Product × Country * Cost Plan > Region × Brand * Marketing Plan > Brand Each input reflects how the business actually plans, not an artificial lowest level. 2. Use a common driver for alignment. A consistent driver measure (for example, Revenue Actuals) is used to determine distribution weights across the entire hierarchy. 3. Allocate across hierarchies. The cube automatically spreads each assumption across missing dimensions: * Brand-level > down to Category > Product * Region-level > down to Country * Combined > expanded to Product × Country All allocations follow the driver distribution. 4. Converge to a common grain. All assumptions are aligned to a unified level: Product × Country × Time. 5. Enable unified reporting. Once aligned, assumptions can be combined seamlessly, enabling metrics like: Profit = Revenue − (Cost + Marketing) at the Product × Country level. Planners can work at different levels while ensuring all data converges into a single, consistent analytical model. # Row Model: "The Agile Driver Framework" Row model is plan's agile driver framework for building driver-based planning models. It transforms the rows in a planning sheet into dynamic planning elements called **drivers**. Instead of depending on a complex semantic model or prebuilt measures, a row model lets you build planning logic directly in the planning layer. A row model uses a single row hierarchy, where each member in the hierarchy becomes a potential driver. Typical row hierarchies include: * Country * Cost Center * Chart of Accounts * Product {% hint style="info" %} ### Note **Key concept:** Every row is a potential driver. {% endhint %} Each driver can represent an input, calculation, subtotal, or business outcome. You define relationships between rows by using formulas and aggregations to create a connected planning model. ### How a row model works A row model starts with a row hierarchy that represents the planning dimension. Every member in that hierarchy becomes a planning driver. For example, a single row hierarchy can contain the following rows: * Goods Sold * Cost * Net Profit Each row acts as an individual driver. You can define formulas and relationships between these rows so that changes to one driver automatically update the related business outcomes. The row hierarchy is usually evaluated across planning versions (such as **Actual** and **Forecast**), business KPIs, and other scalar measures. Unlike traditional spreadsheet models that distribute formulas across multiple cells, a row model organizes planning logic into a connected hierarchy of rows, making it easier to maintain and extend. ### When to use a row model A row model is ideal for organizations that use lightweight semantic models, such as flat tables, star schemas, or row transactional views, but need sophisticated planning logic. Because the planning logic resides in the planning layer, you don't need a complex semantic model or numerous calculated measures in the underlying database. ### Benefits of a row model A row model provides the following benefits: * **Rapid deployment** by building planning logic without requiring a complex semantic model. * **Every row becomes a planning driver**, enabling detailed driver-based planning directly within the planning sheet. * **Business-user ownership**, allowing finance and operational teams to add new planning drivers by simply adding new members to the hierarchy instead of waiting for IT changes. * **Flexible planning**, enabling simple source data to be transformed into sophisticated planning models. * **Centralized business logic**, making models easier to understand, maintain, and extend. * **Scenario planning**, allowing you to evaluate different business outcomes by changing driver values. ### Common use cases Use row models for the following use cases: * Financial planning and analysis (FP\&A) * Profit and loss (P\&L) planning * Budgeting and forecasting * Sales planning * Headcount planning * Supply chain planning * Operations planning ### Next step * Create a row model. # Measure Model: "The Semantic Enrichment Framework" Measure Model leverages the native intelligence of your existing, highly developed data models. Instead of forcing you to rebuild calculations, it imports your central corporate DAX measures and empowers business users to enrich and extend them directly within the Fabric Plan visual interface. #### **How it Works** It connects to your existing complex Semantic Models that hold DAX measures. \ Fabric Plan treats each inherited DAX measure as a foundational baseline (or "Driver"), allowing users to build a layer of dynamic Visual Measures directly on top of them without touching the backend database code. #### **Real-World Example** Your underlying semantic model serves up a robust, governed DAX measure like *Revenue Actual*. Inside the Fabric Plan, a sales planner can instantly reference that measure to build a Visual Measure called *Revenue Forecast* for their upcoming sales planning cycle. #### **Key Concept** Every DAX Measure is a baseline Driver, enriched visually. ### Why Planners Choose Measure-Based Modeling #### **The "Extend, Don't Rebuild" Paradigm** If your organization has already invested heavily in a central BI semantic layer, you don't lose that work. You inherit those precise business logic rules and use them as the launching pad for your forecasting models. #### **No-Code Planning Agility** Finance and operational teams can create scenario-specific planning metrics (like What-If Forecasts or Target Overrides) visually within the tool, eliminating the need to wait for IT or BI engineers to write new DAX formulas in the backend. #### **Seamless Reporting & Planning Alignment** Because your Fabric Plan visual measures are directly anchored to your central corporate DAX measures, your actuals and forecasts sit in perfect harmony, removing data discrepancies. ### Benefits of measure model A measure model helps organizations: * Organize measures into meaningful business hierarchies. * Perform simulations at the measure level. * Visualize the cascading impact of changes across measures and dimensions. * Improve planning and decision-making with a consolidated hierarchical view of business performance. # PREDICT: Statistical Forecasting Algorithm The **Predict** is a forecasting feature that generates future values by analyzing historical data and identifying patterns such as trends, seasonality, and historical relationships. It applies statistical forecasting techniques to estimate future values for a specified forecast period and supports configurable forecasting options to meet different planning requirements.\ The behavior of the forecast depends on the *forecasting algorithm*, the *forecasting model* it uses, and the *model order* selected. A **forecasting algorithm** is a mathematical or statistical method that analyzes historical time-series data to identify patterns such as level, trend, seasonality, and relationships between observations, and uses those patterns to estimate future values. A **statistical model** is the mathematical representation of a historical time series created by a forecasting algorithm. It captures characteristics, such as level, trend, seasonality, and relationships between observations, and is used to generate forecast values A **model order** is a set of parameters that defines the structure and complexity of a statistical model. It tells the algorithm how many components or parameters are included in the model.\ \ Below table contains list of Forecast Algorithm, Statistical model and Model order available in Fabric plan
Forecast AlgorithmStatistical ModelModel Order
Trend Decomposition with MSTL
Exponential Smoothing
  • Auto ETS
  • Simple Exponential Smoothing
  • Holt (Double Exponential)
  • Holt (Double Exponential), Damped Trend
  • Holt Winters, Additive (A, A, A)
  • Holt Winters, Additive, Damped (A, Ad, A)
  • Holt Winters, Multiplicative (M, M, M)
  • Holt Winters, Multiplicative, Damped (M, Ad, M)
  • Holt Winters, Multiplicative, Seasonal (M, N, M)
ARIMA
  • Auto ARIMA
  • Non - Seasonal (ARIMA)
  • Seasonal (SARIMA)

Non - Seasonal (ARIMA)

  • ARIMA / (1,1,1)
  • Random Walk / (0,1,0)
  • AR(1) / (1,0,0)
  • IMA(1,1) / (0,1,1)
  • Differenced AR(1) / (1,1,0)

Seasonal (SARIMA)

  • Airline Model (0,1,1,12)
  • General SARIMA (1,1,1,12)
  • Seasonal AR (1,0,0,12)
  • Quarterly (1,0,1,4)
1. **Trend Decomposition with MSTL - Multiple Seasonal Trend decomposition using LOESS**\ Trend Decomposition with MSTL is a time-series decomposition technique that separates a time series into trend, multiple seasonal components, and a remainder using LOESS smoothing. The decomposed components can then be used to improve forecasting accuracy for data with multiple seasonal patterns.\ It is particularly useful for analyzing data with more than one seasonal pattern and can improve forecasting by isolating the underlying structure of the time series.\ \ Use MSTL when your data has: · More than one seasonal pattern. · Long-term trends. · Complex recurring cycles. 2. **Exponential Smoothing**\ Exponential Smoothing is a statistical forecasting algorithm that predicts future values by calculating weighted averages of historical observations, assigning greater weight to more recent data and progressively smaller weights to older data.\ It is commonly used for time-series forecasting and can be extended to model trends and seasonal patterns through methods such as Holt's Linear Trend and Holt-Winters.\ \ Use Exponential Smoothing when your data has: · A relatively stable level. · No significant trend. · No repeating seasonal pattern. * **Simple Exponential Smoothing:** Simple Exponential Smoothing predicts future values by computing a weighted average of past observations, giving the greatest weight to the most recent data, and is best for time series with no trend and no seasonality. * **Holt (Double Exponential):** Holt's (Double Exponential) Smoothing is a forecasting method that models and forecasts future values by estimating both the current level and the underlying trend of a time series, making it suitable for data with a trend but no seasonality. * **Holt (Double Exponential), Damped Trend:** Holt's (Double Exponential) Smoothing with Damped Trend is a forecasting method that models and forecasts future values by estimating the current level and trend while gradually reducing the influence of the trend over time, making it suitable for non-seasonal data where growth or decline is expected to slow. * **Holt Winters, Additive (A, A, A):** Holt-Winters Additive (A, A, A) is a forecasting method that predicts future values by combining an additive error, an additive trend, and an additive seasonal component, making it suitable for time series with a trend and constant-sized seasonal fluctuations. Seasonal fluctuations remain approximately constant in magnitude regardless of the level of the series. * **Holt Winters, Additive, Damped (A, Ad, A):** Holt-Winters Additive Damped (A, Ad, A) is an ETS forecasting method that predicts future values using an additive error, a trend that gradually weakens over time, and a constant additive seasonal pattern, making it suitable for data with trend and constant-sized seasonality where long-term growth is expected to slow. * **Holt Winters, Multiplicative (M, M, M):** Holt-Winters Multiplicative (M, M, M) is an ETS forecasting method that predicts future values using multiplicative error, multiplicative trend, and multiplicative seasonal components, making it ideal for time series where both growth and seasonal fluctuations increase proportionally with the level of the data. Seasonal fluctuations change proportionally with the level of the series. * **Holt Winters, Multiplicative, Damped (M, Ad, M):** Holt-Winters Multiplicative Damped (M, Ad, M) is an ETS forecasting method that predicts future values using multiplicative errors, a gradually weakening trend, and multiplicative seasonality, making it ideal for data where seasonal effects grow with the level of the series but long-term growth is expected to slow down. * **Holt Winters, Multiplicative, Seasonal (M, N, M):** Holt-Winters Multiplicative Seasonal (M, N, M) is an ETS forecasting method that predicts future values using multiplicative errors, no trend, and multiplicative seasonality, making it suitable for stable time series with seasonal effects that change proportionally with the level of the data. * **Auto ETS:** Automatically evaluates multiple ETS statistical models and selects the model that best fits the historical time series using statistical selection criteria. After running forecast, you can optimize it by choosing the following **parameters**: * **Alpha (α)** – Level Smoothing Parameter: Alpha controls how quickly the model updates the current level (average) based on the newest observation. The Parameter range of Alpha is **(0 < α < 1)**. * **Beta (β)** – Trend Smoothing Parameter: Beta controls how quickly the model updates the trend. The Parameter range of Beta is **(0 < β < 1)**. * **Gamma (γ)** – Seasonal Smoothing Parameter: Gamma controls how quickly the seasonal pattern is updated. The Parameter range of Gamma is **(0 < γ < 1)**. * **Phi (φ)** – Damping Parameter: Phi controls how much the trend is reduced (damped) into the future. The Parameter range of Phi is **(0 < φ < 1)**. 3. **ARIMA (AutoRegressive Integrated Moving Average)**\ ARIMA is a forecasting algorithm that predicts future values by combining information from past observations (AutoRegressive), differencing the data to achieve stationarity, and past forecast errors (Moving Average), making it well suited for non-seasonal time series forecasting.\ \ Use ARIMA when your data: · Has no seasonal pattern. · May have a **trend** that can be removed through differencing. · Shows **autocorrelation**, where past values help predict future values. · Contains sufficient historical observations to estimate the model. · Requires short- to medium-term forecasting based on historical patterns. * **Non Seasonal ARIMA:** Non-Seasonal ARIMA is the standard ARIMA forecasting method that predicts future values using past observations, differencing, and past forecast errors, and is designed for time series that have trends but no repeating seasonal patterns. You can also choose the **model order:** * **ARIMA / (1,1,1):** ARIMA(1,1,1) is a non-seasonal forecasting model that first differences the data once to remove the trend, then predicts future values using one previous observation and one previous forecast error. * **Random Walk / (0,1,0):** Random Walk (ARIMA(0,1,0)) is the simplest ARIMA model, which differences the data once and predicts the next value as the current observed value, assuming future changes are random. * **AR(1) / (1,0,0):** AR(1), or ARIMA(1,0,0), is a forecasting model that predicts the next value using only the immediately previous observation, assuming the data is already stationary and has no trend or seasonality. * **IMA(1,1) / (0,1,1):** IMA(1,1), or ARIMA(0,1,1), is a forecasting model that removes the trend by differencing the data once and predicts future values by adjusting for the most recent forecast error, without using autoregressive terms. * **Differenced AR(1) / (1,1,0):** Differenced AR(1), or ARIMA(1,1,0), is a forecasting model that first removes the trend by differencing the data once and then predicts future values by modeling the relationship between consecutive changes using one autoregressive term.
Model OrderDescription
ARIMA (1,1,1)Uses both recent observations and recent forecast errors to forecast non-seasonal data with a trend.
Random Walk (0,1,0)Assumes the future value will be similar to the latest observed value.
AR(1) (1,0,0)Uses the most recent observation to predict the next value.
IMA(1,1) (0,1,1)Uses recent forecast errors to improve predictions after removing the trend.
Differenced AR(1) (1,1,0)Uses recent changes in the data to forecast future values after removing the trend.
* **Seasonal ARIMA:** Seasonal ARIMA (SARIMA) is a forecasting method that extends ARIMA by modeling both non-seasonal patterns (trend and short-term relationships) and repeating seasonal patterns, making it ideal for time series with regular cycles such as monthly, quarterly, or weekly data. You can choose the **model order**: * **Airline Model (0,1,1,12):** The Airline Model (0,1,1,12) is a seasonal forecasting model that removes both trend and yearly seasonality, then uses recent and seasonal forecast errors to predict future values, making it especially effective for monthly data with a repeating annual pattern. * **General SARIMA (1,1,1,12):** The General SARIMA (1,1,1,12) is a seasonal forecasting model that removes both trend and yearly seasonality, then predicts future values using the non-seasonal autoregressive and moving average components together with seasonal autoregressive and seasonal moving average components. * **Seasonal AR (1,0,0,12):** Seasonal AR(1,0,0,12) is a seasonal autoregressive forecasting model that predicts a value using the observation from one seasonal cycle earlier (such as the same month last year), with no seasonal differencing and no seasonal moving average component. * **Quarterly (1,0,1,4):** Quarterly (1,0,1,4) is the seasonal component of a SARIMA model that uses one seasonal autoregressive term and one seasonal moving average term with a seasonal period of four quarters, making it suitable for quarterly data with a stable yearly seasonal pattern.\
Model OrderDescription
Airline Model (0,1,1,12)Models yearly seasonality and trend using recent forecast errors.
General SARIMA (1,1,1,12)Models trend, recent observations, and yearly seasonal patterns.
Seasonal AR (1,0,0,12)Predicts future values using observations from the same season in previous years.
Quarterly AR (1,0,1,4)Models recurring quarterly patterns using previous quarters and seasonal forecast errors.

* **Auto ARIMA:** Automatically evaluates multiple ARIMA model orders and selects the best-fitting ARIMA model using statistical model selection criteria.
Refer the below table for a summary or when you have a question as **Which algorithm to choose?"**
Data PatternRecommended Algorithm
Stable data with no trend or seasonalitySimple Exponential Smoothing
Trend onlyHolt's Linear Trend
Trend gradually weakensHolt's Damped Trend
Trend with constant seasonal variationHolt-Winters Additive
Trend with seasonal variation proportional to the levelHolt-Winters Multiplicative
Multiple seasonal patternsMSTL
Non-seasonal data with autocorrelationARIMA
Seasonal dataSARIMA
Unsure which ETS model to useAuto ETS
Unsure which ARIMA model to useAuto ARIMA
# Top-down Planning Top-down planning helps organizations to transform strategic business objectives into actionable operational plans. You can create plans at higher levels of the business hierarchy and distribute planning values across lower levels. This approach aligns business units with corporate priorities while reducing planning effort. The planning model applies consistent allocation logic across the planning hierarchy to streamline enterprise-wide planning, accelerate planning cycles, improve consistency, and maintain alignment with strategic goals. ### How top-down planning works Top-down planning follows a structured process: 1. Define planning values at higher hierarchy levels. 2. Evaluate the planning hierarchy. 3. Distribute planning values to lower hierarchy members by using allocation rules. The planning model automatically propagates planning values throughout the hierarchy until it completes the allocation process. ### Why use top-down planning? Top-down planning helps organizations: * Align operational execution with strategic business objectives. * Accelerate enterprise planning across complex hierarchies. * Standardize planning through consistent allocation logic. * Reduce manual planning effort. * Improve planning consistency across business units. * Deliver faster planning cycles while maintaining organizational alignment. ### Common business scenarios Use top-down planning to: * Allocate enterprise budgets across business units. * Cascade corporate revenue and sales targets. * Distribute departmental operating budgets. * Allocate workforce and headcount targets. * Plan production, inventory, and capacity across locations. * Translate executive strategies into operational plans. ### Key capabilities Top-down planning enables you to: * Create plans at summary hierarchy levels. * Distribute planning values automatically. * Apply configurable allocation logic. * Maintain planning consistency across the organization. * Review and refine allocated values before finalizing the plan. ### Key takeaway Top-down planning transforms high-level business strategy into detailed, actionable planning values by intelligently distributing targets throughout the planning hierarchy. Think of Top-down Planning as ‘One to Many’ allocation. # Bottom-up Planning Bottom-up planning helps organizations to create accurate, data-driven plans by capturing planning values at the most detailed level of the business hierarchy. You can create or update planning values at lower hierarchy level, and the planning model automatically consolidates those values into higher-level summaries. This approach gives decision-makers visibility into organizational performance. Organizations use bottom-up planning to leverage operational insights, improve forecast accuracy, and create reliable plans that reflect real business activities across the enterprise. ### How bottom-up planning works Bottom-up planning follows a structured process: 1. Create or update planning values at detailed hierarchy levels. 2. Evaluate the planning hierarchy. 3. Aggregate planning values to parent hierarchy members. 4. Review, refine, and finalize the consolidated planning results. The planning model automatically rolls up planning values through the hierarchy to produce accurate summary-level plans. ### Why use bottom-up planning? Bottom-up planning helps organizations: * Build plans using detailed operational data. * Improve forecast accuracy with business-level insights. * Automatically consolidate planning values across the hierarchy. * Reduce manual aggregation and consolidation effort. * Increase confidence in planning decisions through detailed analysis. * Create reliable enterprise-wide plans from operational inputs. ### Common business scenarios Use bottom-up planning to: * Forecast sales by product, customer, or region. * Plan departmental expenses and operating costs. * Estimate demand by warehouse or distribution centre. * Plan production across manufacturing locations. * Create workforce and resource plans by team or department. * Consolidate detailed planning data into enterprise-wide plans. ### Key capabilities Bottom-up planning enables you to: * Create plans at detailed hierarchy levels. * Aggregate planning values automatically. * Consolidate operational plans into higher-level summaries. * Analyze planning results at both detailed and summary levels. * Review and refine consolidated planning values before finalizing the plan. ### Key takeaway Bottom-up planning transforms detailed operational inputs into accurate, consolidated plans by automatically aggregating planning values throughout the business hierarchy. Think of Bottom-up Planning as ‘Many to One’ aggregation. # Reforecasting Reforecasting lets you update forecast periods using values from existing periods as a baseline. You can copy values from a source period to a target period and then apply adjustments, such as growth assumptions, to create an updated forecast with extending time. Reforecasting is useful when you extend the forecast horizon (planning period) after closing a period and need to seed the newly opened period with the latest actual values. Alternatively, it also allows you to retain the forecast values fed previously in the closing period. ### How reforecasting works Reforecasting follows these steps: 1. Close the completed period and extend the forecast horizon. 2. Select the newly opened forecast period as the target period. 3. Select the measure and source period to use as the baseline. 4. Copy the source values to the target period. 5. Apply adjustments to the target values, such as a growth assumption. ### When to use reforecasting Use reforecasting when you need to: * Input values in the opened forecast period with values from an existing period. * Use recent actuals as a baseline for future forecasts. * Update forecast values after closing a period. * Apply growth assumptions to newly seeded forecast values. * Maintain a continuous forecast horizon as actual periods become available. * To distribute forecast deficit value to open period in the forecast measures. ### Key Takeaway Reforecasting lets you use existing planning values to seed future forecast periods and apply updated assumptions. When you combine reforecasting with period closing and forecast extension, you can maintain a continuous forecast horizon as actuals become available. Deficit distribution also comes handy when you want to retain the total (original) forecast for the year by spreading the delta in the open period. # How-tos # Create a database connection This article helps you to create a database connection. This is a one-time action performed during the initial creation and can be reused later. ### Prerequisites Before you set up Planning sheets, make sure you have the following prerequisites in place: * Data in a [Fabric SQL database](https://learn.microsoft.com/en-us/fabric/database/sql/overview) ### Create a database connection 1. In your Fabric toolbar, select the **Settings** icon. Select **Manage connections and gateways** ![](/files/kAzGiqO1c3H2hKZjl4FJ)
2. Select **New**. 3. Select **Cloud**. 4. Enter a **Database Connection name**. 5. For **Connection type**, select *SQL database in Fabric*. 6. Set the **Authentication method** to *OAuth 2.0*. 7. Select **Edit credentials**, then sign in with your Microsoft account. 8. Select **Create** to create the connection. ### Share the database connection You can share the created connection and manage the user permissions. Click **Manage users**. 1. Next to the name of the connection in your Fabric workspace, select **...** and **Manage users**.
2. Edit the permissions and share as needed.
Now that your database connection is created, you can create a Planning sheet that uses this connection: [Create a Planning sheet](/documentation/readme/creating-a-planning-sheet). # Forecast data to predict future trends Forecasting capabilities in the Planning sheet enable organizations to move beyond static annual plans and adopt agile planning methods, such as rolling forecasts and periodic re-forecasting. Use forecasting to project revenue, expenses, and other metrics for upcoming periods based on historical data. Create dynamic forecasts directly on semantic models and update them as new actuals become available. Forecasts can be generated using multiple approaches, such as copying historical values, applying averages, or manually adjusting projections. ### Prerequisite The column dimension is a standard date hierarchy (for example, year > quarter > month). ### Define initial forecast settings The first step in configuring a forecast is to set the time frame for which the forecast is generated. Then configure how to populate static values in the forecast measure for past or closed periods. For instance, if you have actuals for 2025 and are generating a forecast for 2026, a static forecast measure will be created for 2025. {% hint style="info" %} The forecast measure for previous periods cannot be edited. Static values for closed forecasts can be sourced from a measure in your planning sheet by setting **Closed Period** to **Measure**. Set **Closed Period** to **Formula** to define a formula to populate closed periods. {% endhint %} 1. Go to **Model** > **Forecast** to create a forecast. 2. Set the start and end date of the forecast period. 3. Choose how to fill closed forecasts. Set **Closed Period** to **Formula** and enter the formula. When creating a formula, you can reference other measures in the planning sheet.
Static closed forecasts are populated using the specified formula.
### Options to populate open forecasts Generate open forecasts using one of these methods: * **Measure**- If measure values exist for an open period, use those values to populate the forecast. * **Formula**- If measure values exist for an open period, use a formula based on those values to populate the forecast. * **Data Input**- Allow users to enter forecasts.
When open forecast measures are configured as **Data Input**, you can initialize the forecast using historical or current data. If the source data contains blank values, configure a default value using one of the options shown. {% hint style="info" %} When a measure or formula is selected as the default value, ensure that data is available for the forecast period. {% endhint %}
### Populate open forecasts from the data source If forecast values are already available for future periods in the data source, you can populate open periods using the native forecast measure or a formula that references it. To initialize the open forecast from a measure in the planning sheet, set **Open Period** to **Measure**, then select the measure from **Linked Measure**. To set forecast values based on a formula, set **Open Period** to **Formula** and enter the formula.
Consider a business case where the revenue projections are available for the forecast period, in this case, 2026.
To create a forecast based on a formula, set **Open Period** to **Formula** and enter the formula.
Create and save the forecast.
### Initialize a forecast using historical or current data Prepopulate future forecast periods using existing historical or current data. These initial values can then be manually adjusted by selecting and editing the cell. 1. Set **Open Period** to **Data Input** and **Default Value** to **None.** {% hint style="info" %} If the measure used to initialize the forecast contains null values, you can replace them with a default value. The default value can be a static value, another measure, or a formula. {% endhint %} 2. Select **Create**.
3. In **Period Setup**, From **Copy Source**, select the measure to use to prepopulate the forecast. 4. **Apply Operation** is set to **Period Range,** and **Source Periods** are automatically populated. The Revenue measure from January–December 2025 is used to initialize the forecast for January–December 2026. {% hint style="info" %} The period range duration should match the target period duration. E.g., if the target period is 6 months, then you must select a period range spanning 6 months. {% endhint %}
The forecast is initialized using revenue from the corresponding month in the previous year.
* To initialize forecasts with the average measure value over a specified period range, set **Apply Operation** to **Average of Period Range** and select the timeframe from **Source Periods**.
The average revenue from Q4 2025 is used to initialize each month in the 2026 forecast.
* To initialize forecasts with measure values from a single period, set **Apply Operation** to **Single Period** and select the period.
The revenue from December 2025 is used to initialize each month in the 2026 forecast.
### Initialize forecast ranges using different methods Split a forecast period into multiple ranges and initialize each range using a different method, such as average values or data from a prior period. {% hint style="info" %} This option is available only when **Open Period** is set to **Data Input**. {% endhint %} 1. Configure the initial forecast settings, then go to **Period Setup.** 2. Set the **Target Period** to January-March 2026, **Copy Source** to the Revenue measure, **Apply Operation** to **Single Period**, and **Source Periods** to December 2025.
3. Select **Add Range**. 4. Set the **Target Period** to April-June 2026, **Copy Source** to the Revenue measure, **Apply Operation** to **Average of Period Range**, and **Source Periods** to January-December 2025.
5. Select Add Range. 6. Set the **Target Period** to July-August 2026, **Copy Source** to the Revenue measure, **Apply Operation** to **Period Range**, and **Source Periods** to November-December 2025.
The 2026 forecast is split into multiple ranges based on the period setup configurations: * The forecast for January-March 2026 is initialized from the December 2025 revenue.
* The forecast for April-June(Q2) 2026 is created from the average revenue from 2025.
* The forecast for July-August 2026 is based on the revenue from November-December 2025.
* The forecast values for September through December 2026 are blank because no initial value is configured in the Period Setup. ### Update open forecasts After a forecast is created, its initial values can be modified at any time. If the open forecast has been split into multiple periods, you can update the values for a specific period without affecting the configured values for the other periods. 1. Select **Reforecast** > **Reforecast Column.** 2. Define the period range used to update the open forecast. 3. Select the measure and method to populate the forecast.
The new configuration is applied to the July forecast.
# Create multi-dimensional forecasts Forecasts are often maintained separately by region, product, or other business dimensions, leading to inconsistencies across forecast views. Cube‑based forecasting makes it possible to generate and distribute forecasts across multiple dimensions with varying levels of granularity in a single step. As forecasts are updated, values remain synchronized across all related dimensions. ### Configure additional dimensions Forecasts are generated for the row dimensions included in the planning sheet. To forecast additional dimensions tha are not present in the current sheet, add breakdowns. 1. Click **Add Breakdown** in the forecast configuration.
2. The first breakdown is set to the row dimensions in the sheet. To configure the cube, select **Add** to define the breakdown dimensions and choose a reference measure to drive allocation weights. Select **Create** to create the cube. {% hint style="info" %} The dimensions and measures used to configure the cube can be sourced from the dataset without being added to the current sheet. The reference measure used for weighted allocation should have values for the future forecast period. {% endhint %}
3. Complete the forecast configuration and create the forecast. After the forecast cube is created, it will appear in the **From Sheets** section of the **Data** pane. The forecast can then be imported into other sheets with different granularities without creating separate forecasts for each sheet.
3. To import a forecast cube into a different planning sheet, go to **Data** > **From Sheets** and select **More(...)** > **Insert as a measure** for that cube.
After the forecast cube is inserted into the planning sheet, updates made in either sheet sync automatically, streamlining the process of forecasting across dimensions.
4. Forecast values are distributed evenly across dimensions in the second sheet. Select a cell to redistribute values based on the measure weights in the second sheet.
# Manage rolling forecasts Rolling forecasts continuously extend the forecast horizon. When actual data is available for a forecasted period, the forecast is closed, and the report displays the actuals. ### Close forecasts Once a forecast is closed, it can no longer be edited. Forecasts should be closed as actuals become available. {% hint style="info" %} To hide closed forecasts, de-select **Period** > **Show Closed Periods**. {% endhint %} 1. Go to **Model** > **Period**. Select **Close Period**. 2. If multiple forecasts exist, select the forecast for which the period should be closed. 3. Select the forecast period to close—current or previous year, quarter, or month. A custom period can also be specified.
1. Select **Preview.** The open and closed periods are displayed. Select **Save**.
The forecast is closed for January and cannot be edited.
### Extend forecast periods As forecast periods are closed, new future periods are added to maintain a rolling forecast. 1. Go to **Model** > **Period**. Select **Close Period**. 2. Select the period to close. 3. Select **Extend Forecast Range** and set the duration.
4. Review the closed and open periods. Select **Save**.
As the open forecast for April 2026 is closed, a rolling forecast is created by simultaneously extending the forecast into 2027.
# Generate Statistical Forecasts using PREDICT feature Statistical forecasting uses historical data to identify trends, seasonality, and other statistical patterns to generate forecasts automatically without requiring manual calculations. The Predict feature enables forward-looking analysis by estimating future values from historical observations. It supports configuration of confidence interval, seasonality, growth factor, algorithm selection, and hierarchy evaluation methods (Top-Down or Bottom-Up), allowing forecasts to be generated across planning hierarchies. Forecast results can be reviewed in both graphical and tabular formats. ### How forecasting works The Predict feature uses the selected cell of the measure as a time series. It analyzes historical data within the selected historical date range and generates estimated values for the selected forecast period.
During forecasting, the system considers the following characteristics of the time series: * **Level:** The underlying baseline value of the series. * **Trend:** A sustained upward or downward movement over time. * **Seasonality:** A repeating pattern that occurs at regular calendar or operational intervals. * **Past-value relationships:** The extent to which previous observations help explain future values. * **Past-error relationships:** The extent to which previous forecast errors improve future predictions. ### Prerequisites Before running a statistical forecast, ensure the following: * A forecast measure exists with either blank initial values or prepopulated values. * The planning sheet contains sufficient historical data. * For monthly data, at least **24 months** of historical data are required. * For quarterly data, at least **24 quarters** of historical data are required. * Some forecasting algorithms require **36 or 48 historical periods** to produce more reliable forecast results. ### Configure statistical forecasts 1. Create a planning model by assigning **row** fields, **column** fields, and measures under the **value** field. Consider the model in the following image.
2. Create a new forecast measure (for example, *2026 Forecast*) by selecting the required forecast period through December 2026.
3. Select the *All Row Total* cell of the *2026 Forecast* measure. 4. Navigate to **Model,** Select **Predict**.
***Row Selected** represents the hierarchy member for which the forecast will be generated.*\ ***Measure Selected** is used as the historical input series, and forecast values are written into this measure.* *These fields are automatically populated based on the selected planning sheet cell. Selecting another visible cell updates the selected row and measure in the Predict panel.* *Lock the selection to prevent choosing another cell from changing the selected row or measure.*
5. Specify the date ranges used for forecasting. ***Historic Data Range** specifies the historical periods used to build the statistical model. By default, all closed periods are selected.*\ ***Forecast Date Range** specifies the future periods for which forecasts are generated. By default, all open periods are selected.* *You can modify both ranges to suit your business requirements.* {% hint style="info" %} The forecast period can extend beyond the open period. This is useful when only a short open planning horizon exists (for example, Q1 only), but forecasts are required for a longer period to support planning and forecast validation. {% endhint %}
6. Select an existing forecast profile or create a new one by selecting **More Options (⋯)**. *A **Profile** is a saved collection of forecast settings that helps standardize forecast execution.*\ *Profile includes:* * *Confidence (%).* * *Growth Factor (%).* * *Hierarchy evaluation.* * *Negative-value handling.* * *Algorithm choices.* * *Seasonality choices.* *You can create, duplicate, delete, or reset profiles. Use descriptive profile names so forecast configurations can be easily reproduced.*
7. Set the **Confidence (%).** ***Confidence (%)** controls the requested width of the forecast confidence interval. It represents the probability that the actual value will fall within the predicted range.*\ *Typical confidence% choice include:* * ***80%:** Produces a narrower confidence interval suitable for central planning.* * ***90%:** A balanced confidence level for most business forecasts.* * ***95%:** Produces a wider, more conservative confidence interval.* 8. Set the **Growth Factor (%).** ***Growth Factor (%)** applies a business adjustment to the statistical forecast. It also defines the expected rate at which the forecast values increase or decrease over time.*\ *Examples:* * ***5%** increases forecast values to reflect an expected business uplift.* * ***-5%** decreases forecast values.* * ***0%** preserves the statistical forecast without adjustment.* 9. Assign the **Evaluation** method to. ***Bottom Up** generates forecasts at detailed hierarchy levels and aggregates them into higher-level totals.* ***Top Down** generates forecasts at higher hierarchy levels and distributes them into lower levels.* 10. Set **Round all negative values to zero** as ***Yes,** when the forecasted measure cannot be negative.*\ ***No,** when negative values are valid for the business scenario.*
11. Choose a forecasting **algorithm** that best matches the characteristics of your historical data. The following forecasting algorithms are available: * **Trend Decomposition with MSTL -** Breaks down complex data with multiple repeating patterns into simple parts so they are easier to handle. * **Exponential Smoothing -** Focuses on smooth trends and clear, single-cycle seasons, giving more weight to recent data. * **ARIMA -** Looks at recent spikes, drops, and lags to project where the trend is heading next. For more information about forecasting algorithms, statistical models, and model orders, refer [here](/documentation/readme/planning-sheets/concepts/predict-statistical-forecasting-algorithm)*.* 12. Seasonality is a repeating pattern in data that occurs at regular time intervals, such as yearly or quarterly or both. \ Choose **Set Seasonality** as:\ ***Year**, when the pattern repeats on an annual cycle.*\ ***Quater**, when the pattern repeats on an quarterly cycle.*\ *Both when the pattern repeats on annual and quarterly cycle.* {% hint style="info" %} The seasonality will not display the last scale of time dimension {% endhint %} 13. Select **Run Forecast** to generate forecast values.
14. The forecast preview is available in both graphical and tabular formats: * Historical values are displayed in **grey**. * Forecast values are displayed in **green**. * The shaded green band represents the selected confidence interval.
15. If necessary, select **Reconfigure** or **Re-run Forecast** to adjust forecasting parameters and improve forecast accuracy. \ When you are satisfied with the forecast, select **Save Forecast**.
16. You can also apply the forecast values to a planning scenario or export it to a CSV file.
17. Forecasts are generated for the selected measure and row category. As the Bottom-Up approach is used, values are automatically aggregated to the parent levels.
#### Choosing the right forecasting algorithm Refer the below table for a summary or when you have a question as **"Which algorithm to choose?"**
Data PatternRecommended Algorithm
Stable data with no trend or seasonalitySimple Exponential Smoothing
Trend onlyHolt's Linear Trend
Trend gradually weakensHolt's Damped Trend
Trend with constant seasonal variationHolt-Winters Additive
Trend with seasonal variation proportional to the levelHolt-Winters Multiplicative
Multiple seasonal patternsMSTL
Non-seasonal data with autocorrelationARIMA
Seasonal dataSARIMA
Unsure which ETS model to useAuto ETS
Unsure which ARIMA model to useAuto ARIMA
# Extend planning beyond source data periods Data input fields are essential for planning, where targets, adjustments, and key drivers are entered to define budgets, forecasts, and scenarios. Use the data input to enter values for current and historical periods available in the source data. The **Extend Time** feature combines data input and forecasting to enable data entry beyond the periods available in the source data. ### Prerequisites * The planning sheet must have at least one data input measure. * Forecasts should not be created. ### Extend timelines for data input measures. 1. Select **Extend Time**. 2. Select the start and end periods to extend the data input measures to the specified time range.
# Edit forecast settings Keep forecasts aligned with reporting cycles by editing the forecast period, managing closed periods, configuring aggregation, and defining how actuals update forecast results. ### Editing forecasts Go to **Planning**> **Insert Column** > **Manage Measures** and select the edit icon for the forecast measure. {% hint style="info" %} Forecasts can be edited by selecting Edit Measure from the column gripper. {% endhint %} * To extend or shorten the open period, select the end period from **Forecast Period**.
The open forecast is shortened till Q3, and the Q4 forecast is removed.
* Use **Closed Period Range** to reopen forecasts. Select the period up to which closed forecasts should be reopened. {% hint style="info" %} Select **Reset closed period range** to reopen all closed forecast periods. {% endhint %}
The Q2 forecast is reopened.
* To change the values used to populate open and closed forecasts, select **Re-configure** and edit the default value configuration.
### Set forecast aggregation for column totals When the grand total or subtotal columns are enabled, configure how the forecast measure total is calculated. * Set **Aggregate total** to **All Periods** to calculate the total as the sum of open and closed forecast periods.
* Set **Aggregate total** to **Closed Periods** to calculate the total as the sum of closed forecast periods.
* In the same way, set **Aggregate total** to **Open Periods** to include only open forecasts in the total. ### Control forecast updates from actuals Define how the forecast responds as actuals are loaded. Choose to replace forecast values with actuals or keep forecast values unchanged.
To replace forecasted values with actuals as they become available, the **Closed Period** configuration must be set to **Measure**.
* Choose **Overwrite forecasts** to replace forecasted values with actuals when the forecast for that period is closed.
* To preserve forecasted values, choose **Retain forecasts**.
* Select **Retain blank values** to leave empty forecast cells unchanged when the forecast is closed.
When the forecast is closed, blank values are replaced with actuals unless explicitly retained.
# Allocate deficits to meet targets After a forecast is defined, actuals may fall below the forecast. The shortfall between the forecasted and actual values is called the deficit. If the forecast represents a committed target (quarter/year), a shortfall in early periods needs to be recovered later to still meet the target total. * Without distribution, the total forecast drifts downward unless someone manually adjusts later periods. * With distribution, Plan shifts the shortfall to remaining periods so the overall target remains consistent. ### Distribute deficits 1. Select a forecast cell, then select **Distribute Deficit**. {% hint style="info" %} Distribute a deficit to the total cell to adjust the overall total, or distribute it to child cells to spread the adjustment across lower-level forecasts. {% endhint %} 2. Select the measure to compare the forecast against. The deficit is automatically calculated and displayed.
3. If you have multiple forecast measures in your report, select the forecast measure to distribute the deficit. 4. Select the calendar icon, then select the periods to distribute the deficit across.
5. Select the **Distribution Method**: distribute the deficit equally or by the weights of the forecast. 6. Select **Run** to distribute the deficit across the selected periods.
# Forecasting FAQ This FAQ addresses common questions and clarifications that arise while working with Forecasting. It covers rolling forecast concepts, statistical forecasting configuration, closing and extending the forecast horizon, reforecasting open periods, and writing the finalized forecast to the Fabric SQL database. ### **What is the difference between a closed period and an open period in a forecast measure?** A closed period reads from an existing measure — typically actuals — so the forecast reflects what actually happened. An open period accepts data input, either manual entry or values copied from another measure during period setup. As time moves forward, you progressively close periods to replace forecast values with actuals, keeping the full-year view accurate. ### **Can I have more than one forecast measure on the same planning sheet?** Yes — you can create multiple forecast measures and display them as separate columns on the same sheet. Each forecast measure has its own open and closed period configuration. ### **Can I delete a forecast measure and start over?** Yes — go to **Planning** > **Manage Measures** to delete the forecast measure. This removes the measure and all associated open period configurations from the planning sheet. ### **Why do I need at least two years of historical data for the statistical forecast?** The algorithm needs sufficient data to reliably detect seasonal patterns. With less than two years of data, the model cannot distinguish a true seasonal pattern — for example, a Q4 uplift every year — from a one-time event. Two years give the model one full seasonal cycle to learn from and one to validate against. ### **What does the Confidence % setting control?** The confidence interval defines the range within which the algorithm expects the true value to fall, given the variability in historical data. A 90% confidence interval means the algorithm is 90% confident that the actual value will fall within the shaded range shown in the forecast preview. A higher confidence produces a wider range; a lower one produces a narrower range. The point estimate — the central line — does not change; only the uncertainty band changes. ### **What does the Growth Factor do — is it applied on top of the model's prediction?** Yes — the Growth Factor applies an additional uplift on top of the model's statistically derived values. Setting it to 4% instructs Predict to grow its output by 4% before saving, reflecting a business assumption about market growth that the historical data alone wouldn't capture. ### **What is the difference between Bottom-Up and Top-Down evaluation?** Bottom-Up calculates the forecast at the most granular level first and aggregates up to the highest dimension category and total. Top-Down calculates at the top level and distributes down. Bottom-Up is generally more accurate for granular sales data because each child category's seasonality pattern is captured independently. Top-Down is faster but smooths over brand-level differences. ### **What is the difference between the available forecasting algorithms?** * **Trend Decomposition with MSTL** splits the series into separate layers — one per seasonal cycle plus a trend — forecasts each layer independently, then combines them. It is the best choice when data has patterns repeating at multiple levels, such as both annual and quarterly cycles in beverage sales. * **Exponential Smoothing** predicts by weighting recent observations more heavily than older ones — a good general-purpose choice for most business data, including sales and demand. * **ARIMA** models the statistical structure of the series based on how each value relates to past values and past forecast errors — more technical and better suited to data with strong autocorrelation patterns. ### **What does the Set Seasonality setting under Customize Algorithm do?** **Set Seasonality** tells the model which cycle lengths to detect and account for when generating the forecast. Select **Year to** instruct the model to look for patterns that repeat annually — for example, a consistent Q4 uplift each year. Select **Quarter** to add a second layer, capturing patterns that repeat within the year at the quarterly level. ### **What does the forecast preview show before saving?** Historical data is shown in grey, while predicted values are shown in green, with the confidence range displayed as green shading in the background. This allows you to visually assess whether the model's output looks reasonable before committing the values to the forecast measure. ### **Can I re-run the statistical forecast with different settings after saving?** Yes — you can re-run Predict on the same measure with different settings. The new values will overwrite the previously saved forecast values for the selected date range. ### **Can I apply the statistical forecast to a subset of categories rather than the full hierarchy?** Yes — you can filter the rows before running Predict, or configure the row selection in the **Predict** dialog to target a specific subset. The Bottom-Up evaluation will then run only for the rows in scope. ### **What happens when a period is closed?** The closed month is locked — it populates automatically with the linked measure and turns grey to indicate it cannot be edited. Any forecast assumption previously entered for that month is replaced by the actual value. ### **Can I edit the closed period values?** No — closed period values cannot be edited. They will be greyed out, indicating that those values are non-editable. ### **What do “Overwrite forecasts” and “Retain forecasts” mean when closing a period?** Overwrite forecasts replace the closed period forecast values with actual performance data — this is the default and the most common choice for a rolling forecast, since you want actuals to be the single source of truth for closed periods. Retain forecasts keeps the original forecast values and only fills in actuals where forecast values are blank. Use Retain when you want to preserve the forecast for variance analysis alongside actuals. ### **If I close a forecast and the actuals are slightly different from my forecast, does the grand total change?** Yes — closing a period replaces the forecast value with the actual value, so the full-year total will reflect the actual figure. This is expected and correct behavior. The variance between forecast and actuals is visible when you show both measures side by side. ### **Can I close multiple periods at once, or only one at a time?** You can close a custom range — not just the previous month. In the **Close Period** dialog, select **Custom** under **Close Period Till** and specify the end date of the range you want to close. This is useful if you are catching up after a gap or closing an entire quarter at once. ### **What does Extend Forecast Range do when closing a period?** Extend Forecast Range automatically adds one period to the end of the forecast horizon when you close a period. This is what makes it a true rolling forecast — for example, closing January 2026 and extending by one month adds January 2027, so the forecast always covers the same forward-looking window. ### **What happens if I close a period without selecting “Extend Forecast Range”?** The period closes, and actuals populate correctly, but the forecast horizon shrinks by one month. You would need to manually extend it later using Reforecast. ### **After closing a forecast period, can I reopen it if there is a correction to the actuals?** Reopening a closed period is not a standard operation — it would require adjusting the close period configuration. In practice, actuals corrections are typically handled in the source system, and the close period is re-run. ### **What is the difference between reforecasting and editing the forecast measure directly?** Reforecast opens a new period setup dialog that lets you reconfigure the open period behavior — for example, changing the copy source or growth assumptions — and then redistributes values across the remaining open periods accordingly. Editing cells directly is faster for a single adjustment, but does not recalculate the full distribution. Use **Reforecast** when assumptions change broadly; use direct editing for targeted corrections. ### **What does “Apply Operation – Period Range” mean in the Period Setup dialog?** It maps each target month to its corresponding source month within the defined range. It's a positional copy — the first month of the target range gets the value from the first month of the source range, and so on. ### **If I apply a +3% growth at the sub-total level, does it apply uniformly across all sub-categories or proportionally?** Proportionally — each child category within a parent category receives the growth distributed according to its share of the category total, so the existing distribution is preserved. If it needs to be distributed based on any other measure or equally, this can be configured after entering values in the cells. # Commenting and collaboration Commenting and collaboration features enable you to add contextual discussions directly within the planning sheets. Use these capabilities with your team to review data, provide feedback, assign tasks, and track discussions all within the planning sheet environment. Data-level commentary is commonly used in analytical and planning scenarios. The Planning Sheet provides built-in support for notes, annotations, and collaborative comments, which are dynamically associated with specific data points such as cells, rows, columns, or report sections that remain linked to the relevant data context even when filters or hierarchies change. ### Prerequisites * A planning sheet with the required dataset is saved. * You have appropriate [**user permissions**](#configure-comment-access) to create, reply to, or manage comments. ### Add data-level comments Data-level comments let you add discussions to a specific **cell**, **row**, or **column** in a report. 1. Select the **cell**, **row**, or **column** where you want to add a comment. 2. Select **Comments** from the toolbar or **Add new comment** from the **Comments** dropdown.
3. Enter the comment in the **comment editor**. 4. Apply formatting, such as color, links, or text styling, as required.
5. You can make a comment important by selecting the star icon, which turns it into a starred comment. 6. Select **Assign to User** to assign a user from the dropdown list as needed. 7. Select the **Send** icon to post the comment.
The comment is saved along with metadata such as **author name** and **timestamp**. ### Mention users and collaborate You can notify other users by mentioning them in a comment. 1. Select the cell and add a comment. 2. Type **@** followed by the user’s name. 3. Select the user from the list of suggestions. 4. Post the comment by selecting the send icon. Mentioned users receive Teams notifications with a link to the sheet so they can respond or take action.
Comments help you assign and track tasks for effective workflow collaboration. The task status is initially **Open** and can later be updated to **Resolved** when the task is completed. ### Lock, resolve, and reopen comment threads You can manage comment threads to control discussions. **Lock a thread** 1. Hover over the comment indicator in the planning sheet to open the comment thread. 2. Select the **three-dot** menu and then select **Lock Thread**. Locked threads are greyed out, and no one can edit or reply to them unless they are unlocked. Unlock the thread by selecting the unlock icon. **Resolve a thread** 1. Hover over the comment indicator to open the comment thread. 2. Select the **three-dot** menu and then select **Resolve Thread**. You can reopen a resolved thread if further discussion is needed. Reopen the thread by selecting the undo icon or by replying to the thread.
### Reply to comments Comments support threaded conversations that allow multiple users to collaborate. 1. Hover over the comment indicator to open the comment thread. 2. Enter your response in the **Reply** editor. 3. Post the message by selecting the send icon. Replies appear as part of the same comment thread, making it easier to track discussions. ### View all comments You can view all comments in a centralized panel to track discussions across headers, rows, columns, and cells. To view all the comments: 1. Select **View all comments** from the **Comments** dropdown. 2. The **All Comments** panel opens with the **Data Level** tab selected by default.
Comments are organized by category, along with the number of comments in each category: * **Header Comments**: Comments added at the header level. * **Row Comments**: Comments associated with specific rows. * **Column Comments**: Comments associated with specific columns. * **Cell Comments**: Comments added to individual cells. Expand each section by selecting **>** to view the associated comments. ### Add report-level comments Report-level comments allow users to discuss the entire report instead of a specific data point. To view report-level comments: 1. Select **View all comments** from the **Comments** dropdown. The **All Comments** panel opens. 2. Select the **Report Level** tab. 3. Enter the comment in the side panel. 4. Select **Add Comment**.
Report-level comments support the same capabilities as data-level comments, including mentions, replies, formatting, notifications, and task assignments. ### Configure comment settings You can customize how you want to display comments and configure notification settings. To open the **Comment Settings** pane: 1. Select **Comments** on the toolbar. 2. Expand the **Comments** dropdown, and then select **Settings**. The following options are available: * **Enable Commenting** - Turn commenting on or off for the planning sheet. * **Comments Column** - Show or hide the dedicated **Comments** column. For more information, see [**Add a comments column**](#add-a-comments-column). * **Rollup Indicator** - Display or hide rollup indicators that summarize comments when hierarchical rows are collapsed. * **Star indicator for starred comments** - Display or hide star indicators for comments marked as starred. * **Indicator Size** - Specify the size of the comment indicator that appears in the planning sheet in pixels. * **Indicator Position** - Choose where the comment indicator is displayed within a cell. * **Preview** - Preview how the selected indicator size and position appear. * **Teams Notification** - Turn Microsoft Teams notifications for comments on or off. * **Delete all comments** - Permanently remove all comments from the table. * **Reset** - Discard any unsaved changes and restore the previously saved settings. 3. Finally, select **Save** to apply the changes made in the **Comment Settings** pane.
{% hint style="warning" %} #### Note Deleting all comments permanently removes all comment threads from the table. This action cannot be undone. {% endhint %} ### Add a comments column You can add a dedicated **Comments** column to capture row-level discussions. When a comment is assigned as a task, **Assignee** and **Status** columns are also added to display the assigned user and the current task status. Comments added in the **Comments** column are grouped under **Row Comments**. To add a comments column: 1. Enable the **Show comments column** toggle in the [**Comment Settings**](#configure-comment-settings) panel. A **Comments** column is added to the table. 2. Double-click the required row in the **Comments** column. The comment editor opens. 3. Enter the comment, and then select the **Send** icon to post it. Comments in the **Comments** column support replies, user assignments, and task status tracking. To show or hide the **Assignee** and **Status** columns, select the **>** icon in the **Comments** column header.
To remove the **Comments** column, turn off the **Show comments column** toggle in the **Comment Settings** pane. ### Configure comment access You can control who can access, manage, and interact with comments using the **Security** settings. #### Open comment security settings 1. Select **Security** from the toolbar. 2. In the left pane, select **Comments**. The Comments settings page opens.
#### Manage comment access Use the following options to define access: * **Who can view comments**\ Specify users who can view and interact with comments. Enter names or email addresses to grant access. * **Who can lock or unlock comments**\ Define users who can lock or unlock comments. * **Who can star comments**\ Specify users who can mark comments as important (starred). {% hint style="info" %} #### Note Report authors and editors can delete comments from other users (such as, inappropriate comments). {% endhint %} #### Save or discard changes * Select **Save** to apply changes. * Select **Cancel** to discard changes. # Notes in Commenting and Collaboration Notes are designed for **contextual explanation and documentation**, helping users interpret data, justify values, and improve decision-making. Planning enables **data-level annotation**, allowing users to document insights at granular levels such as rows, columns, or individual cells. ### Prerequisites * You have access to a Planning sheet. ### Add a note to a cell 1. Select the cell to add a note in your Planning sheet. 2. Go to **Planning > Notes > Add New Note.**
3. Enter your **note, apply formats,** and click **Save**.
The note is now associated with the selected data point. {% hint style="info" %} Note You can select multiple cells and add a single note that applies to all of them. {% endhint %}
4. The note can be seen by hovering over the indicator. 5. The Pencil icon on the Note box can be used to edit an existing note. 6. The Delete icon on the Note box can be used to delete an existing note.
### Add notes at row or column level 1. Select the row or column header, and navigate to **Notes > Add New Note.** 2. **Enter a note** in the corresponding Notes field.
The note applies to the entire row or column and is visible across related data.
### Add report-level notes 1. Go to **Planning > Notes > Report Summary**
2. **Enter your note** using the text editor. 3. **Apply formatting** such as: * Bullet points * Bold or italic text * Hyperlinks 4. **Save** your changes. This note provides a high-level summary for the report.
### Add footnotes Footnotes display notes collectively at the bottom of the report for easy reference. 1. Add notes to cells, rows, or columns. 2. Open the **Notes** menu. 3. Enable the **Footnote** option. ![](/files/4byV88Vix5ZW5l03rqMI) All notes are displayed as numbered references in the footnote section.
### Use the notes column The notes column provides a structured way to enter notes for each row by providing a dedicated column to capture row-level annotations. 1. From the **Notes** menu, enable the **Notes column**. 2. Enter notes directly for each row.
### Hide notes You can hide notes to simplify the report view. 1. From the **Notes** menu, enable **Hide all notes**. All notes are hidden from view, and note creation is disabled while this option is active.
### View all notes A consolidated panel or view displays all notes associated with the report. 1. Go to the **Notes** menu. 2. Select **View all notes**.
### Configure note settings Use note settings to customize how notes appear and behave. 1. Select **Notes** > **Settings**.
2. Configure options such as: * Indicator display (style and size) * Indicator position within the cell * Rollup Indicator * Footnote Border * Footnote height * Marker Setting 3. Apply your changes. You can adjust how note indicators are displayed and positioned within cells ### Use marker mode Marker mode allows you to highlight specific data points for emphasis. Marker mode lets you visually mark data for quick identification. 1. Go to **Notes > Marker Mode** 2. Mark the cells, rows, or columns using the marker icon 3. Use the **clear option** to remove markings if needed. 4. Customize color, shape, and width of markings from **Notes Settings**
# Extend Planning sheet with data input Data input rows and columns enable authors to extend the Planning sheet by entering values directly within it. These inputs support planning, forecasting, and operational scenarios where certain values must be captured manually or adjusted within the sheet. Using data input rows and columns, you can capture business inputs that aren't available in the underlying dataset or need manual adjustments while maintaining the report’s hierarchy, totals, and calculations. ### When to use data input rows and columns Data input rows and columns are useful when you need to: * Capture manual adjustments or planning values. * Add business metrics that aren't available in the dataset. * Insert placeholder rows for future categories or products. * Allow business users to enter data directly in the Planning sheet. For example, a financial report might retrieve revenue and expenses from a database but require manual entry for values such as shares outstanding or newly introduced product categories. ### Prerequisites Before you create data input rows or columns, make sure that you have the following prerequisites in place: * The Planning sheet is configured with the required dataset. * You have **edit permissions** in the Planning sheet. * The Planning sheet includes the **row or column hierarchy** where data input is to be added. ### Insert a data input row 1. Select a row in the sheet where you want to insert the new row. 2. Go to **Insert Row** and select the **row type** to be inserted.
3. Insert a **Data Input Number** row. 4. Enter the **Title** for the **Static Row** and configure the row properties.
5. Select **Create**. 6. The new row is inserted after the selected row and becomes available for manual data entry.
### Configure row properties When creating a data input row, you can configure the following properties. **Insert As** * **Single**: Single row is inserted. * **Templated**: Multiple rows are inserted across dimension hierarchies. **Scaling factor** Specifies the numeric scaling applied to the row values, such as thousands or millions. The default option is **Auto**. **Include in total** Determines whether values entered in the row contribute to parent totals or grand totals. **Distribute parent value to children** When enabled, values entered at a parent level are automatically distributed across child rows. **Bind for cross-filter or RLS** Ensures that **cross-filter selections and row-level security (RLS)** rules apply to manually inserted rows. This action prevents users from viewing data outside their permitted scope.
These settings help control how users interact with manually inserted rows. ### Insert a data input column #### Types of data input columns Planning sheet supports various types of data input columns to be inserted, depending on your needs. * **Formula**: A calculated column that derives values using formulas. * **Number**: A column for numeric input, including integers, decimals, currency, or percentages. * **Simulate**: A column for entering values used in scenario simulations or adjustments. * **Text**: A column for free-form text input. * **Checkbox**: A boolean input column for true/false or checked/unchecked values. * **Person**: A column to select or assign a person from a predefined list. * **List**: A dropdown input column allowing selection from predefined options. * **Date**: A column for selecting or entering dates. * **Audit**: A tracking column that logs changes and user actions for auditing purposes.
### Steps to insert a data input column 1. Go to **Planning > Insert Column**. 2. Select the data input column you want to configure. 3. Insert a data input **Number** column. You can insert an empty series and enter your values, or copy from another series in the sheet.
4. Enter the **Title** and configure the properties. Select **Create**. 5. The Number input data column is created. 6. Double-click and enter the values in the cell.
Similarly, you can insert all other types of Data input measures/columns. #### Configure column properties Columns have the following configurable properties: * **Title**: Name of the input column displayed in the Planning sheet. * **Insert as:** * **Visual Measure -** A column is added at each column hierarchy. * **Visual Column -** A single column is added at the end outside the column hierarchy. * **Input type**: Specifies the data type for the column (such as *Number*). * **Row aggregation type**: Defines how values roll up across hierarchy levels (such as *Sum*). * **Distribute parent value to children**: Automatically allocates parent values proportionally to child members. * **Enable Multi-Dimension Allocation**: Allows splitting values across multiple dimension breakdowns. * **Minimum Value**: Sets the lowest allowable input value for validation. * **Maximum Value**: Sets the highest allowable input value for validation. * **Static Value**: Set a fixed value.
# Multidimensional planning and forecasting # Plan across multiple dimensions with cubes Traditional multidimensional planning solutions often require complex data models, backend processing, and ongoing maintenance to support partitions, staging environments, and dependent measures. Fabric Plan allows you to create multidimensional cubes directly on your semantic model and simplifies planning by eliminating the need for backend cube modeling.
#### Allocate data across multiple dimensions Cubes enable multidimensional allocations for plans, budgets, forecasts, and other data input measures. * Use reference measures and dimensions that are not part of the current report. * Allocate values based on the proportional weights of a driver measure, such as revenue, headcount, or historical sales. * Distribute values accurately across business units, products, regions, or time periods. #### Synchronize planning sheets Cubes support bidirectional synchronization between connected planning sheets at different levels of granularity. * Automatically aggregate changes from detailed planning sheets into the original planning sheet. * Distribute changes from the original planning sheet to connected planning sheets based on configured allocation rules. * Keep summarized and detailed plans synchronized while preserving the defined allocation logic. This approach simplifies complex allocation scenarios while ensuring that plans remain consistent across multiple dimensions and planning models. Learn more about how [cubes and multi-dimensional allocations work](/documentation/readme/planning-sheets/concepts/cube). In this module, you learn how to configure cubes for [multidimensional expense allocation](/documentation/readme/planning-sheets/how-tos/multidimensional-planning-and-forecasting/multidimensional-expense-allocation). # Multidimensional expense allocation Data input cubes enable multidimensional planning by connecting planning sheets with different dimensional granularities, allowing data to flow between summarized and detailed plans. Instead of maintaining separate planning sheets and manually reconciling changes, data input cubes automatically distribute updates to detailed planning sheets based on allocation rules and aggregate changes back to higher-level plans. Business plans are often created at a higher level of the organization but must be allocated to lower levels for detailed planning and analysis. In this article, learn how to use cubes to allocate OPEX from the country level to cities and products using multidimensional allocations.
### Prerequisites * The column dimension is a standard date hierarchy (for example, year > quarter > month). ### Configure a cube When you create a data input measure, define one or more breakdown dimensions to convert it into a cube measure. The measure can then store and allocate values at multiple levels of dimensional granularity. 1. In the **Planning** ribbon, go to **Insert Column** > **Number** > **Insert a new empty series**. For more information, see [Create a numeric data input measure](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/insert-manual-input-columns). 2. Select **+Add Breakdown** from the **Enable Multi-Dimensional Allocation** section. This action opens the **Add Breakdown** window where you configure the cube. 3. To allocate values based on the weights of a driver measure, select the required measure from **Reference measures**. In this example, use *Revenue Actual* as the reference measure. To learn more, see [weighted allocation](/documentation/readme/planning-sheets/concepts/cube#how-allocation-works). {% hint style="info" %} The reference measure doesn't need to be part of the current planning sheet. You can select any measure from your semantic model as long as it doesn't have any null values for the breakdown dimensions. If the reference measure has null values for any of the breakdown dimensions, allocation fails. {% endhint %}
4. Allocate plans, budgets, and forecasts across dimensions that aren't included in the current planning sheet by adding breakdowns. The row dimensions in the current planning sheet are automatically added in the first breakdown- in this example, *Region\_Name* and *Country\_Name*. Select additional dimensions, such as City\_Name, Channel\_Name, and Product\_Name, to distribute values at a finer level of granularity. {% hint style="info" %} Cubes supports planning across unrelated dimensions. For example, updates made to a revenue plan by product, geography, and channel can automatically flow to finance dimensions such as GL Account and Country, enabling seamless bidirectional allocations and enterprise-wide planning across business functions. {% endhint %}
In this example, the product-level planning sheet also contains region dimensions, so we've added the product-level dimensions to the same breakdown. Select **Add** to create new dimension breakdowns as shown in the following image.
5. Create the breakdown and the measure. 6. After you create a cube measure, you can see it under **Data** > **From Sheets** > **Cube**.
7. Enter a value at the grand total level. In this example, we've copied the *Operating Expense Actual* value so that we can use the cube to allocate this value across additional dimensions. Plan automatically distributes the value equally among child rows and columns. Select the distribution icon to choose an alternate distribution method.
8. Create a calculated measure to compute *Gross Margin* at the region level. {% hint style="info" %} This step is optional. It is not required to create cubes. {% endhint %}
### Import a cube measure into another planning sheet After you configure a cube measure, you can use it in other planning sheets with different granularities based on the configured breakdowns. In this example: * You create a cube in a region-level plan. * You configure breakdowns on city, sales channel, and product dimensions. * You enter data for the cube measure. * You import the cube measure into the product-level plan. The values you enter in the region-level planning sheet allocate to the product dimensions based on the weights of the reference measure. When you import the cube into the product-level planning sheet, plan automatically populates the entered values. {% hint style="info" %} When you import a cube measure, the row dimensions in the planning sheet must be a subset of the dimensions configured in the cube breakdowns. The dimensions can be in any order. For example, the cube breakdown is configured with the dimensions Region, Province, and City. The planning sheet can include any subset of these dimensions, such as Region > City or Province. {% endhint %} 1. Go to **Data** > **From Sheets** > **Cube**. Select the measure to import. From **More options (...)**, select **Insert as measure**. Alternatively, in the **Model** ribbon, go to **Cube** > **Import Cube Measure** and select the measure to import.
This action imports the cube measure into the product-level planning sheet. Organizations often plan values at higher levels of the business hierarchy and allocate them to lower levels for detailed planning. In this example, a cube allocates operating expenses (OPEX) from the region level to product-level dimensions.
2. Plan distributes cube measure values equally to breakdown dimensions such as *city*, *sales channel*, *product family*, and *product*. Select the distribution icon to choose an alternate distribution method. {% hint style="info" %} Redistributing imported cube values affects only the unrelated dimensions and preserves existing allocations. {% endhint %}
This action redistributes imported cube values across the product dimensions while preserving region-level allocations. The following screenshot demonstrates how the cube value remains unchanged for the *Asia* and *China* rows even after redistribution.
3. In the **Planning** ribbon, go to **Show Columns** and deselect the measures that are not required for planning - in this example, the native measure *Operating Expense Actual*. 4. Create a calculated measure to compute *Gross Margin* at the product level. {% hint style="info" %} This step is optional. It is not required to create cubes. {% endhint %} ### Update cube values Cubes support bi-directional updates between planning sheets of different granularities. In this example, update the *Opex Allocation* for China from 24.3m to 25m in the region-level planning sheet.
The cube allocates the updated value to the additional dimensions in the product-level planning sheet. Plan automatically recomputes calculated measures, such as *Gross Margin*, to reflect updates to cube values.
Similarly, the cube automatically aggregates updates made at a lower level of granularity and propagates the aggregated values to the planning sheet at the higher level of granularity. ### Best practices for setting up a cube * *Use a valid allocation driver*: Always configure a single, clearly defined allocation driver (reference measure) such as prior year actuals, revenue, or units. Ensure the driver reflects real-world business weighting logic. * *Allocate only across nonblank driver cells*: Allocation only occurs where the selected driver measure has valid (nonblank) values. Avoid allocations across intersections where the driver is null, as it can cause allocation errors or unintended distributions. * *Restrict input by using **Allow Input – Based on Formula***: To prevent users from entering or allocating values on invalid intersections, configure an input rule such as `[Driver Measure]!==BLANK`.
By configuring **Allow Input – Based on Formula**, cells that don't satisfy the specified condition are automatically locked. This configuration ensures that users can allocate only at dimension intersections that meet the defined driver criteria (for example, where the reference measure is nonblank). # Multidimensional forecasting In enterprise planning, high-level targets (such as revenue or budget) must be distributed across complex business structures—like regions, product lines, departments, and time periods. Multidimensional or cube forecasting automates this process by linking data across different levels of detail into a unified model. Learn more about [creating forecasts](/documentation/readme/planning-sheets/how-tos/forecast-data-to-predict-future-trends). Instead of manually maintaining separate planning sheets for each dimension, cube forecasting enables real-time distribution and aggregation across multiple dimensions simultaneously. In this article, you learn to allocate a revenue target forecast defined at the region level to product and sales channel dimensions. #### Prerequisites * The column dimension is a standard date hierarchy (for example, year > quarter > month). ### Configure a forecast cube When you create a forecast, define one or more breakdown dimensions to convert it into a forecast cube. The cube can then store and allocate forecast values at multiple levels of dimensional granularity. In this example, * You create a planning sheet at the region level * You create a profit forecast cube and configure dimensional breakdowns * You import the forecast cube into a product-level planning sheet * You update the forecast in the product-level planning sheet and use bidirectional updates in cube to aggregate it to the region-level planning sheet. 1. In the **Model** ribbon, select **Forecast,** enter the forecast measure name, and set the forecast period.
2. Enable the **Multi-Dimension Allocation** toggle. Select the driver measure from **Reference Measures**. Driver measure weights allocate forecasts across dimensions. Learn more about [weighted allocations in cube measures](/documentation/readme/planning-sheets/concepts/cube#how-allocation-works). {% hint style="info" %} Reference any measure in the semantic model as the weighting measure for allocation. The measure does not need to be included in the current planning sheet. Ensure the reference measure contains values for the forecast period in the current planning sheet. Otherwise, cube creation fails during allocation. {% endhint %} In this example, *Previous Year Units* is used as the reference measure. Although it's not assigned to the current planning sheet, you can still use it to determine the allocation weights.
3. A breakdown defines how a forecast is allocated across different dimension hierarchies (such as *Region > Country > Sales Channel > Product Line*) using a reference measure for proportional weighting. The row dimensions assigned to the planning sheet are automatically treated as the first breakdown. {% hint style="info" %} The top-level row dimension in the current planning sheet is required to create breakdowns for dimensions that are not included in the sheet. In this example, *Region\_name.* {% endhint %} To allocate values across additional dimensions, select **+ Add** to create a breakdown. Define breakdown dimensions.
4. Select **Next**. Closed forecasts for previous years are static, and you can't edit them. Configure the measure or formula to populate closed forecasts.
5. In this example, to create a zero-based forecast, set the **Open Periods** configuration to **Data Input** and select **Save**.
6. After you create the forecast cube, you can see it in the **From Sheets** section of the **Data** pane.
7. Enable **Column Subtotal** in the **Planning** ribbon and enter the forecast total value for 2026. Plan automatically distributes the value equally among child rows and columns. Select the distribution icon to choose an alternate distribution method. In the following steps, you use bidirectional cube updates to modify the forecast in a different planning sheet at a different granularity. The cube automatically aggregates the updates and propagates them back to the *Global Target Revenue* planning sheet.
### Import a forecast cube into a different planning sheet After configuring a cube measure, it can be used in other planning based on the configured breakdowns. For instance, a cube created in a region-level plan can be imported into a product-level plan. 1. Create a second planning sheet and assign measures and dimensions. {% hint style="info" %} When importing a forecast cube, the column dimensions in the second sheet must match those of the original sheet. The row dimensions in the planning sheet must be a subset of the dimensions configured in the cube breakdowns. The dimensions can be in any order. For example, if the cube breakdown is configured with the dimensions Region, City, Channel, Product Line, the planning sheet can include any subset of these dimensions, such as Channel > Product Line or Region > City > Product Line. {% endhint %} In this example, the original planning sheet uses *Year* > *Quarter* > *Month* as the column hierarchy, so the second planning sheet uses the same column dimensions. One of the breakdowns is *Region* > *Channel* > *Product Family* > *Product*. The second planning sheet uses *Channel, Product Family, Product* as the row dimensions.
2. Go to **Data** > **From Sheets** > **Cube**. Select the cube measure to import. From **More options (…)**, select **Insert as measure**. This action imports the forecast cube measure.
3. Plan distributes forecast values equally to breakdown dimensions such as *sales channel*, *product family*, and *product*. Select the distribution icon to choose an alternate distribution method.
4. Cubes support bi-directional updates between planning sheets of different granularities. Update a value in the cube measure in the second planning sheet. In this example, add a 10% increase to the *Target Revenue Forecast* at the channel level.
5. The cube aggregates the updated value and propagates it to the region-level planning sheet. The new forecast value overwrites the forecast entered in step 7. In the same way, any updates made to the original planning sheet cascade to child sheets.
# Cube best practices and FAQs ### Best practices while setting up a cube * *Use a valid allocation driver*: Always configure a single, clearly defined allocation driver (reference measure) such as prior year actuals, revenue, or units. Ensure the driver reflects real-world business weighting logic. * *Allocate only across nonblank driver cells*: Allocation only occurs where the selected driver measure has valid (nonblank) values. Avoid allocations across intersections where the driver is null, as it can cause allocation errors or unintended distributions. * *Restrict input using **Allow Input – Based on Formula***: To prevent users from entering or allocating values on invalid intersections, configure an input rule such as `[Driver Measure]!==BLANK`.
By configuring **Allow Input – Based on Formula**, cells that don't satisfy the specified condition are automatically locked. This ensures that users can only allocate across dimension intersections that meet the defined driver criteria (for example, where the reference measure is nonblank). ### FAQ #### What is a Cube measure, and how is it different from a regular measure? A regular measure pulls a value directly from the semantic model or a planning sheet at whatever granularity it exists. A Cube measure is a calculated allocation layer - it takes a measure with limited granularity and distributes it across additional dimensions using a reference measure as the basis for distribution, without altering the source data. #### Can I add more than one breakdown to the same Cube measure? Yes - you can add multiple breakdowns, which are useful when you need to segregate planning into groups such as geography, category, or customer type. However, all breakdowns must have a common dimension. #### If I create a data input cube measure using the Copy from another series option, and the base measure is later updated, does the cube measure change too? No - the base measure only populates the cube measure at the point when it is created. After that, the cube measure becomes its own independent editable series. A later change to the base measure would not retroactively update the cube - the two are no longer linked once the copy is made. #### What would happen if you tried to pull a cube measure into another sheet using From Sheets instead of Cube Measures? The values would not populate. Without the Cube breakdown, Infobridge has no way to resolve the measure at the additional dimensions - it can only read the measure at the grain at which it was created. This is the core problem Cube Measures solves. #### When a subtotal value is updated, why do individual category combinations under the subtotal value update too? The update is applied to the subtotal, and the cube breakdown distributes that change proportionally down to every dimension category combination under the subtotal, based on their share of the reference measure. #### Why do I need to use the Insert as measure option to import cubes, rather than pull in the same way other referenced measures are? Inserting it specifically as a Cube measure is what preserves its two-way behavior. If it were pulled in as a regular read-only reference, the consuming sheet would only display the value - it can't be edited there. Inserting it through Cube Measures keeps the breakdown-based link intact, so an edit on the consumer flows back to the origin just as an edit on the origin flows forward to the consumer. # Manage cube measures Use the cube options to manage cube measures, synchronize them with the semantic model, modify breakdown dimensions, and control how cube measures are reused across planning sheets. The **Data** pane provides separate menus for the entire cube and for individual cube measures, enabling administrators and planners to perform bulk operations or manage specific measures independently. ### Modify dimension breakdowns You configure dimension breakdowns while creating a data input or forecast measure. To edit breakdowns, in the **Model** ribbon, go to **Cube** > **Manage Breakdown** and select the cube measure. Add, remove, or modify the breakdown dimensions associated with the selected cube measure.
### Manage cube measures Use the cube options to synchronize, monitor, and manage all cube measures in a planning sheet. In the **Data** pane, hover over the **Cube** section and select the **More options (…)** menu.
OptionDescription
Sync with DataSynchronize all cube measures with the latest changes in the semantic model.
View Sync LogsView the history and status of synchronization and cube management operations.
DeletePermanently delete all cube measures in the planning sheet. This action also removes linked cube measures from other planning sheets and the Data pane.
Collapse AllCollapse all expanded cube measures.
Expand AllExpand and display all cube measures.
### Manage individual cube measures Use the cube measure options to reuse, synchronize, modify, or delete a specific cube measure. In the **Data** pane, hover over the cube measure and select the **More options (…)** menu.
OptionDescription
Insert as a MeasureInsert the selected cube measure into the current planning sheet.
Sync MeasureSynchronize only the selected cube measure with the latest changes in the semantic model.
Manage BreakdownAdd, remove, or modify the breakdown dimensions associated with the selected cube measure.
DeleteDelete the selected cube measure, remove it from linked planning sheets, and remove its references from the Data pane.
# Analyze planning outcomes with scenarios Scenarios allow you to create multiple planning versions within a Planning sheet to evaluate different business outcomes. You can use scenarios to compare budgets, forecasts, and alternative business assumptions without affecting the base data. ### Prerequisites Before you create a scenario, make sure that you have the following prerequisites in place: * You have access to the Planning sheet. * Required fields and dimensions are added. * The planning model includes a *scenario dimension*. * You have permission to edit planning data. ### Create a scenario 1. Go to **Model > Scenario**. 2. Enter a name to identify the scenario. 3. Select the series to be simulated and select **Create**.
4. Sales values are simulated by adjusting the slider to increase or decrease cell values. Net revenue is recalculated accordingly.
5. Select **Save** to save the scenario after reviewing the results. 6. Select the **+** icon at the bottom of the page to create more scenarios. **Save** the report when you're done.
The new scenario is added to the Planning sheet and is available for planning and analysis. ### Scenario toolbar Use the **Scenario** tab to create, manage, and analyze scenarios in the Planning Sheet.
### Compare scenarios Use **Compare scenarios** to analyze differences between two scenarios across measures, time periods, and dimensions. This view helps you evaluate performance, identify variances, and make data-driven decisions. #### Open scenario comparison 1. Navigate to the planning view. 2. Select **Compare Scenario**. 3. The **Scenario Comparison** view opens.
#### Configure comparison **Compare -** Select the primary scenario you want to analyze. **With -** Select the scenario to compare against. **Measures -** Choose the measures to include in the comparison. * Select **All Measures** or a specific measure. #### Understand the key elements of the comparison view The comparison grid displays data across selected dimensions and time periods. * **Scenarios** – Displays values for both selected scenarios. * **Δ Scenario** – Shows the variance between the two scenarios. * Positive values indicate an increase. * Negative values indicate a decrease. * **Time periods** – Data is grouped by periods such as **Q1** and **Q2**. * **Measures** – Displays values for selected measures (for example, *2025 Actuals*, *2026 Plan*). * **Dimensions** – Rows represent hierarchical categories such as *Category*, *Beverages*, and *Water*. #### Exit comparison * Select **Exit Compare** to return to the standard view.
### Update scenario settings. You can update scenario settings such as the name, included measures, and lock status in **Edit Scenario** under **Scenario > Settings.** * **Scenario name -** Update the name of the scenario. * Under **Include series in scenarios**, select the measures to include: * Choose one or more series (for example, **2025 Actuals**, **2025 Gross Sales**). * Only selected series are included in the simulation. * **Lock the scenario -** Select **Lock Scenario** to prevent further changes to the scenario. {% hint style="info" %} When a scenario is locked, you cannot modify values or settings until it is unlocked. {% endhint %} * Select **Apply** to save changes or select **Cancel** to discard changes.
### Configure input method Use the **Input Method** to simulate and adjust values directly in the Planning Sheet.
#### Simulation * Modify values in the cells by adjusting the slider to increase or decrease cell values to test different outcomes. * Enter a custom value in a cell to apply a specific adjustment.
#### Distribution Distribution enables you to apply consistent or trend-based changes quickly across multiple data points. Use the distribution options to apply values across rows or columns. **Adjust distribution values** * Use the slider to increase or decrease values proportionally. * Enter a custom value in a cell to apply a specific adjustment. **Available options** * **Copy until last row in \**: Apply the value to all rows from the selected category untill the last row * **Copy until last row with trend in \**: Apply values across rows based on an existing trend. * **Copy to all rows in \**: Distribute the value to all rows in the selected category. * **Copy to all rows**: Apply the value across all rows in the sheet. * **Copy until last column**: Apply the value across columns until the last column. * **Copy until last column with trend**: Distribute values across columns using a trend. * Select **Reset value** to revert changes to the original value.
### **Show variance** View differences between scenario values and base values when show variance is checked.
### Slider settings Select **Slider settings** to open the **Variance settings** pane. #### Variance settings options The **Variance settings** pane includes the following options: * **Series**: Displays the series included in the scenario. * **Increase is good**: Enabled by default. * When enabled, increases are shown in green and decreases in red. * When disabled, increases are shown in red. * **Value range**: Specify the maximum value for the range. * The default value is **100%**.
### Reset Revert changes made in the scenario. ### Copy scenario data to base Use **Copy to base** to apply simulated values from a scenario to the base sheet. You can copy scenario values to the base from: * The **Scenario** toolbar by selecting **Copy to base** * The **More options (⋮)** menu next to the scenario tab * Review the selected columns. * Select **Proceed**.
The selected scenario values are copied to the corresponding base measures. After copying, the base report reflects the updated values from the selected scenario. {% hint style="info" %} Only simulated values are copied to the base. Native measures are not copied from scenarios to the base. {% endhint %} ### Bulk edit in scenarios Use **Bulk edit** to apply changes to multiple values in a scenario at once. Use bulk edit to efficiently perform large-scale scenario simulations without updating individual cells. #### Open and Configure bulk edit * Select **Bulk Edit** from **Scenario** * In the **Bulk Edit** pane, configure the following: * **Measure**: Select the scenario measure to update. * **Row dimensions**: Select the required categories and subcategories. * **Column dimensions**: Select the required columns or time periods. {% hint style="info" %} As you select dimensions, the Planning Sheet updates to reflect the filtered data. {% endhint %} #### Apply bulk changes * **Set value** – Assign a specific value to the selected cells. * Use the slider or input box to define the value or adjustment, or specify the value or percentage to apply. * Select **Apply.**
The selected values are updated across the specified dimensions. * **Cancel** – Closes the dialog without saving changes. * **Reset** – Clears all selections and restores default settings. ### **Pivot** Change the layout to analyze data from different perspectives. Use **Pivot** in the **Scenario** tab to create alternate row-level views for analyzing and simulating scenarios. Pivoting allows you to rearrange dimensions, such as Category and Sub Category, without affecting the underlying data. Learn more about pivot in the pivot section. #### Create a pivot 1. Go to the **Scenario** tab. 2. Select **Pivot**. 3. The **Row Pivot** dialog opens. 4. In the **Row Pivot** dialog, select **+ Add** to create a new pivot. 5. Enter a name for the pivot in the **Name** field. 6. In **Available fields**, select the dimensions to include. 7. Add selected fields to the **Fields** section. * Arrange the fields in the required order. 8. Select **Save**. A pivot called pivot 2 is created for subcategories.
### Writeback scenarios Use **Writeback** to persist scenario data from the planning view to the underlying data source. You can write back all scenarios or a selected scenario. Use writeback to: * Save planning inputs and updates * Commit scenario changes to the data source * Share finalized data with other users or systems To writeback scenarios, 1. Go to the **Scenario** tab. 2. Select **Writeback**. 3. Choose one of the following: * **Writeback All** – Writes back all available scenarios. * **Writeback** – Writes back only the selected scenario. 4. Writeback is completed.
{% hint style="info" %} The writeback option is enabled only after the destination is added. You can learn more from the [writeback ](/documentation/readme/planning-sheets/how-tos/persist-planning-data-using-writeback)section. {% endhint %} ### Logs Use **Logs** to track and review all writeback activities. Go to **Scenario** > **Logs**. This view provides detailed information about each writeback operation, including status, duration, and user details.
### Close scenario Exit the current scenario by selecting **Close Scenario** from **Scenario.** ### Configure scenario access You can control who can access, manage, and interact with scenario using the **Security** settings. **Open comment security settings** 1. Select **Security** from the toolbar. 2. In the left pane, select **Scenario**. 3. **Who can access scenario**: Specify users who can view and interact with each scenario. Enter names to grant access. 4. Select **Save** to apply changes. 5. Select **Cancel** to discard the changes
### FAQ #### Is Base itself an editable scenario, or is it a fixed reference point? Base represents the current committed plan and acts as the reference point against which other scenarios are compared. Scenarios like *Best Case* are created as separate simulation layers on top of Base rather than as edits made directly to it. #### After creating a scenario, do its values carry forward into new scenarios? No - each new scenario is created as its own separate simulation layer on top of the original Base figures, not on top of another scenario's adjusted values. #### Can calculated measures that use native measures be simulated? Calculated measures that reference native measures cannot be simulated directly because they do not store independent values. Instead, their values are derived from the underlying native measures using a calculation or formula. #### **How do simulated changes propagate through dependent measures?** When a simulated change is applied to a leaf-level value, the update propagates automatically through all dependent measures in the hierarchy. Parent measures that aggregate child values and calculated measures that reference those aggregations are recalculated in real time. This ensures that every affected value across the hierarchy reflects the simulated change instantly, without requiring manual updates. #### Can more than one scenario exist at the same time without one overwriting the other? Yes - Different scenarios, such as *Best Case* and *Cost Restructuring* can coexist. They are separate and independently referenceable. #### Are scenario simulations permanent once created, or can they be deleted without affecting the base scenario? Simulations exist within their scenario and do not alter Base or the underlying data source values. They can be adjusted or deleted without any permanent impact on the actual plan. # Bookmarks Bookmarks in Planning sheets allow you to save and revisit specific report states. These states can include filters, sorting, layouts, hierarchy expansions, and other configurations. You can create and manage bookmarks to save and quickly access specific views. ### Prerequisite * You have access to the Planning sheet with the required dataset. ### Create a bookmark Create bookmarks to save the current state of your Planning sheet after you apply the required changes to the sheet, such as filters, layout changes, and sorts. 1. Select the **Bookmark** icon and open the **Bookmark** pane.
2. Select **Public** or **Private** and **Add New** to create a new bookmark. {% hint style="info" %} Note Public bookmarks are visible to all users who have access to the sheet. Private bookmarks are visible only to the user who created them. {% endhint %} A new bookmark called **Bookmark 1** is created.
### Manage bookmark options Select **More options** (**…**) next to a bookmark. The following actions are available: * **Update**: Update the bookmark with the current view. * **Make default**: Set the bookmark as the default view. * **Promote to Public**: Make the bookmark available to other users. * **Rename**: Change the bookmark name. * **Delete**: Remove the bookmark. * **Copy link**: Copy a shareable link to the bookmark. ### Common scenarios for bookmarks 1. **Save filtered and sorted views -** Bookmarks retain filters, sorting, and selected measures. 2. **Switch between layouts -**Bookmarks help to switch between layouts to tailor the sheet for different audiences.\ Navigate hierarchies - Bookmarks preserve hierarchy states and formatting for reuse. 3. **Highlight ranked data -** Bookmarks help **to** analyze key contributors and outliers**.**\ Use this to analyze key contributors and outliers. 4. **Save number formatting** - Bookmarks retain formatting preferences such as scaling and precision. # Exports in the Planning sheet Planning sheet allows you to export reports to **PDF** and **Excel** while preserving formatting, layout, and data context. You can export complete sheets or selected data. Exported Planning sheets retain key elements such as: * Planning sheet layout and structure * Cell values and number formatting * Notes and annotations * Filters and layouts * Calculated rows and columns. ### Prerequisite * You have access to the Planning sheets to be exported. ### Export a Planning sheet to PDF Use PDF export to generate formatted, paginated sheets. 1. Go to the **Export** option. 2. Select **Export to PDF**.
3. Select the **Properties** * **Entire Matrix -** The Entire Matrix is exported. * **Selected Columns -** Only the selected columns are exported. 4. Configure [**Advanced Settings**](#configure-advanced-pdf-export-options)**.** 5. Select **Export** and download the file.
### Configure Advanced PDF export options ### **Page Setup:** You can customise the output using page setup settings: * Select **Content Order** * **Row-first**: Data fills across rows first, then continues to the next page. * **Column-first**: Data fills down columns first, then continues to the next page. * Select **page size** (A4, A3, custom sizes) * Select portrait or landscape **orientation** * Select **scaling** to fit content. * Select the **Margin** type.
### **Advanced Settings** * Enable **Image/Logo** to include images. * Use an image as a background * Set margins to align content horizontally and vertically.
* Include comments on the last page. * Include Filter information. * Enable compression to reduce the PDF file size.
### Formatting You can customize formatting using the following options: * Apply **font styles** as needed. * Select the display type for the **Header** and **Footer**. * Set the **decimal precision**. * Configure **Auto wrap** options.
Select **Apply** to save your changes, or select **Reset** to clear all changes. ### Export a Planning sheet to Excel Use Excel export for further analysis and data manipulation. 1. In the Planning sheet, select **Export** from **Planning**. 2. Select **Export to Excel**. 3. Under **Export mode**, select one of the following: * **Fully expanded** - Exports all hierarchy levels in an expanded view. * **With expand/collapse** - Exports data with hierarchy controls to expand or collapse levels in Excel. * **Current state** - Exports the report exactly as it appears, including applied filters and expanded levels. 4. Under **Properties**, select one of the following: * **Entire Matrix -** The Entire Matrix is exported. * **Selected Columns -** Only the selected columns are exported. 5. Configure [**Advanced Settings**](#advanced-settings-in-excel-export-options) as required**.** 6. Select **Export t**o export the Excel file.
6. Select the link to save your file to your local system.
#### Advanced Settings in Excel export options **Header Settings** Planning sheets support exporting header elements such as dates and symbols to Excel. Use header settings to control and preview which header lines are included in the export.
**Footer Settings** Planning sheets support exporting footer elements such as dates to Excel. Use footer settings to control and preview which footer lines are included in the export.
**General Settings** Configure additional options as required: * Freeze headers and label columns. * Smart fit to automatically fit content to columns. * Add a formula appendix sheet.
Select **Apply** to apply the changes and configure your PDF Export settings, or select **Reset** to reset all the applied changes. # Pivot Pivot data lets you reorganize row dimensions to create alternate views of your data without changing the underlying dataset. Each pivot view represents a unique arrangement of dimensions, enabling flexible analysis and planning across different perspectives. With the **Pivot** option in the Planning sheet, you can: * Reorder row dimensions * Show or hide specific dimensions * Create aggregated views using different dimension combinations **Pivot Explorer** saves all pivot views, allowing you to quickly switch between them during analysis. ### Prerequisite * Access to a Planning sheet with row dimensions configured. ### Create and use pivot views 1. Open your Planning sheet. 2. Select **Pivot**.
3. select **Add** 4. Enter a name for the pivot view. 5. Select the required row dimensions. 6. Select **Save**.
The pivot view is added to the Pivot Explorer. ### Manage pivot views Each pivot represents a unique combination of row dimensions. * Select your pivot view.
The Pivot views can be managed by enabling the **Pivot Explorer**. * Edit an existing pivot by updating its name or fields. * Select (**⋮**) to **Duplicate** or **Delete** pivots.
# Layouts Layout options control how data is structured and displayed in the report. You can configure both **table layouts** and **measure layouts** to customize how categories and values appear. ### Prerequisites Before you configure layout options, ensure that you have the following: * Access to a planning sheet with data loaded. * Permissions to edit the Planning sheet. ### Configure table layouts 1. Open the Planning Sheet. 2. Go to **Layout options**. 3. Select the required **Layout.** Various types of [table](#types-of-table-layouts) and [measure layouts](#types-of-measure-layouts) are listed below.
### Types of table layouts Table layouts determine how row categories are presented in the report. ### Hierarchy Displays data in a hierarchical format with expand and collapse functionality. This is the default layout.
### Table Displays data in a tabular format without hierarchical expansion for rows. Column hierarchies can still support expand and collapse.
### Tree layout Use the tree layout to customize the structure and display of your data. The tree layout also helps you visualize absolute and relative variances between measures. 1. Go to **Home > Layout > Tree**. 2. In the **Configure Tree View** dialog, select the **baseline** and c**omparison** measures and **save.**
After saving, the **Tree View** option is added to the toolbar. You can use these options to further customize the tree layout.
### Tree view options The **Tree View** provides the following options: * **Quick format**: Choose the number scaling format. * **Baseline**: Select the baseline measure. * **Comparison**: Select the measure to compare against the baseline. * **KPI**: Select key performance indicator measures. * **Create scenario**: Create and simulate scenarios. * **Compare scenario**: Compare multiple scenarios. * **Display settings**: Customize the tree layout display. * **Node customization**: Preview and customize node scope, style, and components. * **Level configuration:** Customize node style settings at the dimension level. * **Appearance:** Configure font color, primary color, background color, and conditional formatting. ### Node actions You can use the node menu to perform actions such as editing, viewing details, and adding comments. ### Open the node menu 1. Select a node in the view. 2. Select **More options** (**…**) on the node. The node action menu appears. The following actions are available: * **Pin node**: Pin the selected node for quick access. * **Edit node**: Modify node properties such as name, aggregation, and formatting. * **Simulate per period**: Run simulations for the node across periods when you create scenarios. * **View details**: View additional information about the node. * **Add comment**: Add comments to the node for collaboration. * **Simulation lock**: Lock the node to prevent changes during simulation when you create a scenario.
### Edit a node You can edit a node to update its name, aggregation behavior, and formatting. Refer above for display settings. #### Update general settings Under **General**, you can modify the following: * **Row name**: Update the name of the node. * **Include in total**: Include the node in the total calculation. #### Update formatting Under **Formatting**, you can configure how values are displayed: * **Scale**: Select how values are scaled (for example, Auto). * **Decimal points**: Specify the number of decimal places. * **Prefix**: Add a prefix to values. * **Suffix**: Add a suffix to values. #### Apply changes * Select **Apply** to save changes. * Select **Cancel** to discard changes
You can customize individual elements by selecting the **edit node**. Customize each element in the tree layout to meet your needs.
Select a node to view additional information, such as **Trends**, **Details**, **Variance**, and **Dependents**, and to analyze performance over time and compare values against a baseline. * **Trends** – Displays time-based performance. * **Details** – Shows detailed data for the selected node. * **Variance** – Highlights differences between actuals and comparison values. * **Dependents** – Shows dependent elements related to the selected node. **View options** * Select **Graph** to display a visual chart of trends. * Select **Table** to view the same data in tabular format.
### Types of measure layouts
### Types of measure layouts Measure layouts determine how values (measures) are displayed in the report. #### Measures in rows Displays measures as rows instead of columns. You can reorder measures by dragging rows or using the **Manage rows** option.
#### Measures in columns Displays measures across columns above the column hierarchy categories.
### Ragged hierarchy A ragged hierarchy is a hierarchy in which branches have an uneven number of levels. * Ragged hierarchies are also known as unbalanced hierarchies, as they contain uneven or unbalanced levels. * The number of levels depends on the underlying data, as some members don’t have values at all levels. * Use this setting to improve readability in hierarchical views by hiding blank or placeholder rows. ### Turn on ragged hierarchy When ragged hierarchy is turned on, only available levels are displayed, and empty rows are removed. 1. Go to **Layout**. * Select **Ragged Hierarchy**. * Turn on **Ragged Hierarchy**.
When ragged hierarchy is turned on: * **Hide blanks:** Hides missing levels based on value, category, or both. * **Suppress zeros:** Hides rows with zero values. * **Hide blank columns:** Hides columns with no data. When ragged hierarchy is turned off: * All levels are displayed, even if values or categories are missing.
# Conditional formatting Conditional formatting allows you to apply dynamic formatting to data in the planning sheet based on specified conditions, thresholds, or value ranges. It helps improve data visualization by automatically highlighting important values, trends, exceptions, and performance indicators. You can use conditional formatting to apply different formatting styles such as background colors, font colors, data bars, bubble charts, heat maps, classifications, icons, and action indicators. Conditional formatting helps users quickly analyze data and identify patterns without manually reviewing individual values. ### Key capabilities Conditional formatting supports the following capabilities: * Apply formatting based on value ranges or conditions. * Categorize values using classifications. * Display visual indicators such as icons, action dots, and action colors. * Visualize data using color scales, data bars, and bubble charts. * Apply formatting to rows, columns, measures, and charts. * Include formatting in exported Excel and PDF reports. Conditional formatting can be applied using the following methods: * Use quick formatting options such as quick rules, color scales, classifications, and data bars. For more information, see [**quick formatting options**](/documentation/readme/planning-sheets/how-tos/conditional-formatting/quick-formatting-options). * Create advanced formatting rules using nested IF conditions, classification, and other custom logic. For more information, see [**Create rule**](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting). You can manage existing rules by editing, hiding, reordering, or deleting them using [**Manage rules**](/documentation/readme/planning-sheets/how-tos/conditional-formatting/manage-rules). # Create rules in Conditional Formatting Conditional formatting helps you highlight important data points in a planning sheet by applying visual cues such as colors, icons, and styles based on defined rules. This improves readability and directs attention to key performance indicators in high-density tabular reports. ### Create a conditional formatting rule To create a rule: 1. In the Planning sheet navigate to **Format >** **Conditional formatting.** 2. Select **Create rule**.
3. Enter a **Title** for the rule. 4. Select where to apply formatting using **Apply to** (rows, columns, headers, or measures).
5. Select the formatting scope using **Row hierarchy levels**: * Values only * Totals only * Values and totals
6. Select a rule type from **Format by**: * Rules (If conditions) - For more information about configuring rules using IF conditions, see [**Rules (IF conditions)**](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting/format-by-rules-if-conditions-in-conditional-formatting)**.** * Color scale - For more information about configuring rules using Color scale, see [Color scale](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting/format-by-color-scale-conditional-formatting). * Classification - For more information about configuring rules Classification, see [Classification](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting/format-by-classification-conditional-formatting).
6. Select **Apply** to create the conditional formatting rule.
# Format by Rules (If conditions) in conditional formatting When creating conditional formatting rules, you can choose from three **Format by** options that determine how formatting is applied. This article explains the Rules (If conditions) format option in detail. Refer to [create rules in conditional formatting](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting) to know about it. ### Format by Rules (If conditions) In the **Format by** dropdown, select **Rules (If conditions)** to configure rule-based conditional formatting based on one or more logical conditions. Format cells using font styles, colors, borders, icons, or background highlights when specified conditions are met. #### Configure formatting styles In the **Style** section, define how data should appear when conditions are met: * **Font style** – Apply bold, italic, or underline * **Font color** – Change text color * **Cell background** – Highlight cells with color * **Borders** – Add custom borders * **Icons or text** – Display indicators or symbols * **Hide values** – Mask sensitive data when conditions are met You can also: * Align icons within cells * Display only icons or text (hide values) * Apply formatting to charts or labels where applicable.
#### Define rule conditions Use the **Conditions** section to specify when formatting is applied. You can combine multiple conditions using **AND/OR** logic. Each condition contains the following components: | Setting | Description | | ---------------- | ------------------------------------------------------------------------------------------------- | | Measure or field | Select the measure or column to evaluate. | | Operator | Define the comparison logic, such as *Greater than*, *Less than*, or *Equal to*. | | Condition type | Specify the comparison source, such as **Number**, **Data selection**, **Value**, or **Formula**. | | Value | Enter the comparison value or select a reference. | Supported condition types include: * **Number** - Apply formatting based on numeric thresholds. * **Data selection** - Reference another cell value dynamically. * **Value -** Compare against another measure or column. * **Formula** - Use expressions combining measures and values. * **User selection** - Apply formatting dynamically based on runtime user interaction.
#### Nested conditions Advanced conditional formatting can be done by selecting **Add Condition.** * Add multiple conditions and combine them using **AND/OR** logic. * Apply complex business rules across dimensions.
# Page 1 ### Format by Rules (If conditions) In the **Format by** dropdown, select **Rules (If conditions)** to configure rule-based conditional formatting based on one or more logical conditions. Format cells using font styles, colors, borders, icons, or background highlights when specified conditions are met. #### Configure formatting styles In the **Style** section, define how data should appear when conditions are met: * **Font style** – Apply bold, italic, or underline * **Font color** – Change text color * **Cell background** – Highlight cells with color * **Borders** – Add custom borders * **Icons or text** – Display indicators or symbols * **Hide values** – Mask sensitive data when conditions are met You can also: * Align icons within cells * Display only icons or text (hide values) * Apply formatting to charts or labels where applicable.
#### Define rule conditions Use the **Conditions** section to specify when formatting is applied. You can combine multiple conditions using **AND/OR** logic. Each condition contains the following components: | Setting | Description | | ---------------- | ------------------------------------------------------------------------------------------------- | | Measure or field | Select the measure or column to evaluate. | | Operator | Define the comparison logic, such as *Greater than*, *Less than*, or *Equal to*. | | Condition type | Specify the comparison source, such as **Number**, **Data selection**, **Value**, or **Formula**. | | Value | Enter the comparison value or select a reference. | Supported condition types include: * **Number** - Apply formatting based on numeric thresholds. * **Data selection** - Reference another cell value dynamically. * **Value -** Compare against another measure or column. * **Formula** - Use expressions combining measures and values. * **User selection** - Apply formatting dynamically based on runtime user interaction. #### Nested conditions Advanced conditional formatting can be done by selecting **Add Condition.** * Add multiple conditions and combine them using **AND/OR** logic * Apply complex business rules across dimensions
### Format by - Color scale conditional formatting **Color scale** applies gradient-based conditional formatting to values in a planning sheet. Color scales help visualize data distribution and identify high and low values using color intensity. The **Color scale for** lets you select the formatting target. Available options include: * **Background** – Applies gradient formatting to the cell background. * **Font** – Applies gradient formatting to the text color. * **Data Bars** – Displays bars inside cells to represent value magnitude. * **Bubble Chart** – Applies color scaling to bubble chart visuals. * **Action Dots** – Applies color-based indicators using action dots. * **Action Color** - Applies color-based formatting to action or status indicators.
Use **Custom color ranges** to define color ranges based on **values** or **percentages**. You can add or remove ranges as needed. Enabling **Hide Values** hides the values of the cells. The **Middle percentage** setting defines the midpoint used in a gradient scale.
Use **Heat map type** to control how color scale formatting is evaluated across the planning sheet. Available options include: * **Row wise** – Applies color scaling independently for each row based on the values within that row. * **Column wise** – Applies color scaling independently for each column based on the values within that column. * **Table wise** – Applies color scaling across the entire table using all visible values. Select the appropriate heat map type based on how you want values to be compared and visualized.
Use **Color scale type** to select the color distribution pattern used for conditional formatting. Available options include: * **Sequential** – Uses a gradual progression of colors to represent low-to-high values. This type is useful for displaying continuous numeric ranges. * **Diverging** – Uses contrasting colors to highlight variation above and below a midpoint or baseline value. This type is useful for variance and performance analysis. * **Diverging - color safe** – Uses accessibility-friendly diverging colors designed for improved readability and color differentiation. * **Qualitative** – Uses distinct colors to represent categorical or non-sequential data values. * **Qualitative - color safe** – Uses accessibility-friendly qualitative colors for categorical data visualization. * **Continuous - Range** – Applies a continuous gradient across a defined value range. * **Continuous - Diverging Range** – Applies diverging colors across a defined range with emphasis on midpoint variation. * **Continuous** – Applies a smooth color transition between minimum and maximum values. * **Continuous - Diverging** – Uses contrasting colors to highlight values above and below a midpoint or baseline value. * **Custom** – Lets you define custom color ranges and thresholds for conditional formatting.
Use the **Color scheme** section to customize the appearance of the color scale conditional formatting. You can configure the following settings: | Setting | Description | | --------------- | ------------------------------------------------------------------------------------------- | | Color scheme | Select the color palette used for the color scale. | | Reverse color | Reverses the selected color sequence. | | Number of bands | Defines the number of color intervals used in the scale. | | Hide value | Hides cell values and displays only the color formatting. | | Auto font color | Automatically adjusts the font color for improved readability against the background color. | | Include null | Includes null or blank values in the color scale evaluation. |
The **Middle percentage** setting defines the midpoint used in a gradient scale.
# Format by Color scale conditional formatting When creating conditional formatting rules, you can choose from three **Format by** options that determine how formatting is applied. This article explains the Color scale format option in detail. Refer to [create rules in conditional formatting](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting) to know about it. ### Format by - Color scale conditional formatting Color scale applies gradient-based conditional formatting to values in a planning sheet. Color scales help visualize data distribution and identify high and low values using color intensity. The **Color scale for** lets you select the formatting target. Available options include: * **Background** - Applies gradient formatting to the cell background. * **Font** - Applies gradient formatting to the text color. * **Data Bars** - Displays bars inside cells to represent value magnitude. * **Bubble Chart** - Applies color scaling to bubble chart visuals. * **Action Dots** - Applies color-based indicators using action dots. * **Action Color** - Applies color-based formatting to action or status indicators.
Further available conditional formatting options vary based on the selected **Color scale for** option. ### Background color scales The **Background** color scale option applies conditional formatting by changing the background color of cells based on their values. Different colors are used to represent value ranges, making it easier to identify trends, patterns, highs, and lows within the data. Background color scales are useful for heatmap-style analysis and visual comparison across rows, columns, or measures. ### Font color scales The **Font** color scale option applies conditional formatting by changing the font color of cell values based on the underlying data. Colors are assigned according to the configured value ranges, helping highlight important values without modifying the cell background. Font color scales are useful when you want to emphasize values while maintaining the existing cell background or layout formatting. #### Configure conditional formatting for background and font color scales #### **Heat map type** Use **Heat map type** to control how color scale formatting is evaluated across the planning sheet. Select the appropriate heat map type based on how you want values to be compared and visualized. Available options include: * **Row wise** - Applies color scaling independently for each row based on the values within that row. * **Column wise** - Applies color scaling independently for each column based on the values within that column. * **Table wise** - Applies color scaling across the entire table using all visible values.
#### **Color scale type** Use **Color scale type** to select the color distribution pattern used for conditional formatting. Available options include: * **Sequential** - Uses a gradual progression of colors to represent low-to-high values. This type is useful for displaying continuous numeric ranges. * **Diverging** - Uses contrasting colors to highlight variation above and below a midpoint or baseline value. This type is useful for variance and performance analysis. * **Diverging - color safe** - Uses accessibility-friendly diverging colors designed for improved readability and color differentiation. * **Qualitative** - Uses distinct colors to represent categorical or non-sequential data values. * **Qualitative - color safe** - Uses accessibility-friendly qualitative colors for categorical data visualization. * **Continuous - Range** - Applies a continuous gradient across a defined value range. * **Continuous - Diverging Range** - Applies diverging colors across a defined range with emphasis on midpoint variation. * **Continuous** - Applies a smooth color transition between minimum and maximum values. * **Continuous - Diverging** - Uses contrasting colors to highlight values above and below a midpoint or baseline value. * **Custom** - Lets you define custom color ranges and thresholds for conditional formatting.
#### **Color scheme** Use the **Color scheme** section to customize the appearance of the color scale conditional formatting. You can configure the following settings: | Setting | Description | | --------------- | ------------------------------------------------------------------------------------------- | | Color scheme | Select the color palette used for the color scale. | | Reverse color | Reverses the selected color sequence. | | Number of bands | Defines the number of color intervals used in the scale. | | Hide value | Hides cell values and displays only the color formatting. | | Auto font color | Automatically adjusts the font color for improved readability against the background color. | | Include null | Includes null or blank values in the color scale evaluation. | {% hint style="info" %} **Hide value, Auto font color** and **Include null** are available only for Background Color scales {% endhint %} Conditional Formatting rule applied for Background Color Scales.
### Data Bars The **Data bar** color scale option applies conditional formatting by displaying horizontal bars within cells based on the underlying values. The length of each bar represents the relative value compared to other values in the selected range. Use data bars to quickly identify trends, compare values, and highlight high or low-performing data points visually. #### Configure conditional formatting for data bar color scales You can configure the following options for data bar color scales: * **Minimum and maximum values** - Define the range used to calculate bar lengths. * **Bar color** - Select the color used for the data bars. * **Hide value** - Choose whether to display the cell value along with the data bar. * **Alignment -** Configure the position of the data bars within the cell, such as left or right. After configuring the settings, save the rule to apply the data bar formatting to the selected cells. Conditional Formatting rule applied for data bar Color Scales.
### Bubble chart color scales The **Bubble chart** color scale option applies conditional formatting by displaying values as bubbles within cells. The size of each bubble represents the relative magnitude of the value, enabling quick visual comparison across the selected range. Bubble charts help identify trends, outliers, and value distribution in a compact visual format. #### Configure conditional formatting for bubble chart color scales You can configure the following options for bubble chart color scales: * **Hide value** - Choose whether to display the cell value along with the bubble. * **Bubble overlay** - Enable this option to allow the bubble chart to overlap the cell value. * **Bubble color** - Select the color used for the bubbles. * **Apply color to text** - Enable this option to apply the selected bubble color to the cell text. * [**Heat map type**](#heat-map-type) - Select the heat map style to determine how colors are applied to the selected values. * **Measure based color** - The bubble gradient and color intensity are determined based on the minimum and maximum values of the measure selected from the dropdown. After configuring the settings, save the rule to apply the bubble chart formatting to the selected cells.
### Action dots and Action colors The **Action dots** and **Action colors** options apply conditional formatting to action indicators in the planning sheet based on the configured rules and selected values. * **Action dots** indicate the extent to which cell values deviate from the target or desired range in the measure. These indicators help you compare values against the midpoint or a specified target value within the measure. They display visual indicators as colored dots within cells to highlight status, trends, or important changes. The number of dots represents the magnitude of the difference * **Action colors** apply color formatting to action indicators, making it easier to identify different states or conditions at a glance. Action colors are a variation of action dots where the deviation of cell values from the target value is represented using color gradients. The color scale indicates positive or negative deviation, while the gradient intensity represents the magnitude of the deviation. These options help improve visibility and enable quick analysis of planning data. #### Configure conditional formatting for Action Dots and Action Colors color scales * **Custom color ranges -** Use **Custom color ranges** to define color ranges based on **values** or **percentages**. You can add or remove ranges as needed. * **Hide Values -** Enabling **Hide Values** hides the values of the cells. * **Middle percentage -** The **Middle percentage** setting defines the midpoint used in a gradient scale. This is applicable only for Action Dots.
# Format by classification conditional formatting When creating conditional formatting rules, you can choose from three **Format by** options that determine how formatting is applied. This article explains the classification format option in detail. Refer to [create rules in conditional formatting](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting) to know about it. #### Classification The **Classification** format option applies conditional formatting by grouping values into predefined categories or ranges. Each classification can be assigned a specific formatting style, such as colors, icons, or indicators, to visually differentiate values based on their category. Classification formatting helps identify performance levels, status indicators, trends, and value distributions within the planning sheet. ### Configure classification conditional formatting You can configure the following options for classification formatting: * **Display** - Select the display icons from **Text, Rating** or **Icon Set** to categorize values.
* **Apply to charts** - Enable this option to apply the classification formatting to chart visualizations associated with the selected data. * **Show as new column** - Enable this option to display the classification icons in a separate column. * **Icon position** - Configure the position of the classification icon within the cell. It can be to the left of data, to the right of data or just the icon. * **Classification ranges -** Use **Classification ranges** to define color ranges and icons based on **values** or **percentages**. You can add or remove ranges as needed.
After configuring the settings, save the rule to apply classification formatting to the selected cells.
# Quick formatting options Quick formatting options offer a convenient way to apply conditional formatting to the planning sheet without manually creating advanced rules from scratch. After creating a quick rule, you can further customize the rule based on your reporting requirements. These options help you highlight trends, variances, classifications, and value distributions using predefined formatting styles. Quick formatting is useful for quickly visualizing data and improving report readability with minimal configuration. ### Supported quick formatting options The following one-click conditional formatting options are supported: * **Quick rules** - Apply predefined conditional formatting rules based on common conditions. * **Color scales** - Apply gradient-based formatting using various color scale types. * **Classification** - Categorize values into groups and apply formatting based on thresholds or ranges. * **Data bars** - Display horizontal bars within cells to visually compare values. * **Action Analysis** - Applies visual indicators and color-based formatting. ### Apply quick formatting To apply quick conditional formatting: 1. Open the planning sheet. 2. Select the required row, column, or measure. 3. Open the **Conditional formatting** pane. 4. Select the required one-click option. 5. Configure the formatting settings, if required. 6. Save the rule to apply the formatting. ### Quick rules Quick rules allow you to apply commonly used conditional formatting conditions such as: * Greater than * Less than * Equal to * Between ranges * Top or bottom values * Positive or negative values These rules can be applied with minimal configuration. For more information, see [Rules (If Condition)](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting/format-by-rules-if-conditions-in-conditional-formatting). ### Color scales Color scales apply formatting based on value intensity or distribution within the selected range. These options help visualize trends and compare values quickly. For more information, see [Color scales](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting/format-by-color-scale-conditional-formatting). ### Classification Classification formatting groups values into categories and applies visual indicators based on configured ranges or thresholds. For more information, see [Classification](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting/format-by-classification-conditional-formatting). ### Data bars Data bars display horizontal bars within cells to represent relative values visually. The bar length changes dynamically based on the selected value range. For more information, see [Data bars](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting/format-by-color-scale-conditional-formatting#data-bars). ### **Action Analysis** The **Action analysis** option provides visual indicators that help analyze trends, status changes, and performance variations within the planning sheet. The following display options are supported in action analysis: * [**Action dots**](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting/format-by-color-scale-conditional-formatting#action-dots-and-action-colors) - Displays colored dots as visual indicators within cells. * [**Action colors**](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting/format-by-color-scale-conditional-formatting#action-dots-and-action-colors) - Applies color gradients based on configured conditions. * [**Bubble charts**](/documentation/readme/planning-sheets/how-tos/conditional-formatting/create-rules-in-conditional-formatting/format-by-color-scale-conditional-formatting#bubble-chart-color-scales) - Displays the bubble along with the cell value or additional formatting elements. * **Bubble only** - Displays only the bubble visualization without showing the cell value. This provides a cleaner, more compact visual representation focused entirely on value magnitude. ### Manage applied rules Rules created using one-click options can be managed from the **Manage rules** pane. For more information, see [**Manage rules**](/documentation/readme/planning-sheets/how-tos/conditional-formatting/manage-rules). # Manage Rules ## Manage conditional formatting rules The **Manage rules** option allows you to view, edit, reorder, enable, disable, or delete existing conditional formatting rules in the planning sheet. Use the Manage rules pane to organize and control how multiple conditional formatting rules are applied to your data. ### Open Manage rules To open the Manage rules pane: 1. Open the planning sheet. 2. Select **Conditional formatting** from the toolbar. 3. Select **Manage rules**.
### Rule management options The Manage rules pane displays all the conditional formatting rules configured for the current planning sheet. The following actions can be performed from the **Manage rules** pane: #### Add new rule Create a new conditional formatting rule from the **Manage rules** pane. #### Edit a rule Select the pencil icon next to the rule to modify its settings, conditions, formatting options, or target range. #### Enable or disable a rule Use the toggle option to temporarily enable or disable a conditional formatting rule without deleting it. Disabled rules are not applied in the planning sheet but can still be retained for future use. #### Reorder rules Select the **Reorder rules** (⋮⋮) option to control the evaluation priority when multiple conditional formatting rules are applied to the same cells. Rules are evaluated based on their order in the **Manage rules** pane. #### Delete a rule Delete rules that are no longer required from the planning sheet. Deleting a rule permanently removes the associated formatting configuration. #### Duplicate a rule Create a copy of an existing rule to reuse the same configuration with minor modifications. ### Rule priority behavior When multiple conditional formatting rules are applied to the same value, rule priority determines which formatting is displayed. Rearrange the rule order to control formatting precedence.
### Export behavior Conditional formatting rules configured in the planning sheet can also be applied during Excel and PDF exports. Enable or disable rules to be included in the exported report. Learn more about [Exports](/documentation/readme/planning-sheets/how-tos/exports-in-the-planning-sheet) here. # Persist data with Writeback Writeback saves data from a Planning sheet to external data destinations such as databases, cloud data warehouses, and data lake storage. We can write back data such as budgets, forecasts, adjustments, and scenario inputs from the Planning sheet and load it to the writeback table in your data platform. This ensures that planning data remains synchronized with your enterprise systems. Writeback supports multiple destination types and enables you to save data without requiring predefined database schemas or complex setup. ### Writeback use cases Writeback enables you to keep planning and analytics data in a single governed environment instead of exporting data manually. Common scenarios include: * Save budget and forecast inputs directly to your data platform. * Store scenario planning results for further analysis. * Capture manual adjustments made in Planning sheets. * Synchronize planning data with enterprise data warehouses. ### Prerequisites Before you begin, make sure that you have the following prerequisites in place: * The Planning sheet contains at least one row or column dimension. * A valid writeback destination is configured. * Required permissions are assigned to users who perform writeback. ### Create a writeback destination To save data using writeback, you must first configure a destination. 1. Go to **Writeback > Add Destination**. 2. Select a Database connection. 3. Enter a **Database Name**. 4. Enter **a Table Name**. 5. Select the **Decimal Precision**. 6. Select **Add** to create the writeback destination.
### Manage destinations Go to **Writeback > Manage** You can view, update, or reuse configured destinations.
### Set the writeback type You can control how data is structured in the writeback table. Go to **Writeback > Settings > Writeback Type**, and select one of the following: * Select **Long** to store measures as key-value pairs a row-based format. * Select **Wide** to store measures in a column-based format. * Select **Long with Changes**. This is the same as long format, but it tracks only the changed values written back from the Planning sheet. * Select **Wide with Changes**. This is the same as wide format, but it tracks only the changed values written back from the Planning sheet.
### Configure writeback settings Manage other writeback settings from **Writeback > Settings**. The writeback settings are used to configure how data entered in a Planning sheet is saved back to the underlying data source. It ensures that planning data entered by users is stored correctly, consistently, and in a format that aligns with the organization’s data model. It defines the structure, filtering behavior, and data format used when writing planning values to the destination. * **General**: Defines the core settings for the writeback operation. * **Data**: Used to select and configure the measures and dimensions that participate in the writeback process. * **Destinations**: Defines where the planning data is written back, such as a database table or storage location. * **Advanced**: Provides more configuration options for controlling writeback behavior and system-level settings.
### Perform writeback You can write back scenarios, forecasts, data inputs, and comments to the designated data platform. Select **Writeback > Writeback** to trigger a writeback. After the operation completes, a notification confirms the status.
Select **Writeback > Logs** to view the writeback logs.
# Writeback in Plan Plan supports writing back and exporting planning data to multiple destination types, including file-based destinations, data warehouses, and data lake storage. It writes back forecasts, scenario models, and user inputs to backend destinations. Unlike conventional BI and planning tools that require predefined database schemas and IT-managed writeback infrastructure, Plan supports dynamic database configuration and runtime table creation. Writeback structures are generated automatically based on planning context. Plan also supports semi-structured, on-the-fly writebacks and can write to multiple destinations simultaneously. This article covers writeback, adding a destination, performing writeback, and logs. ### Start writeback To access writeback options and start writeback, select the **Writeback** tab.
The **Writeback** tab includes: * **Writeback**: Execute writeback. * **Destination**: Add and manage writeback destinations. * **Data**: Customize data-related options for writeback. * **Logs**: View detailed information for each writeback event. * **General:** Configure writeback-related settings. ### Add destination Add one or more destinations to save data using Writeback.
{% hint style="info" %} On-premises database servers must be directly reachable from the internet for writeback. They must have a public IP address with port forwarding. {% endhint %} Data writeback capabilities are subject to specific row limits depending on the file source or destination used. While local file formats such as Excel and CSV support a significant volume of up to 200,000 rows, cloud-based integrations via OneDrive and SharePoint are restricted to a much smaller capacity of only 250 rows. ### Perform writeback 1. Go to the **Writeback** tab, and then select **Add Destination**.
2\. In the **Create Destination** dialog, select **+ New** next to **Select a Connection** to create a new connection, or choose dropdown to select an existing connection.
3. After selecting the connection, provide the required details: * **Database Name**: Select the target database. * In the **Select destination database** dialog, browse and select the required database from the **OneLake catalog.**
* **Schema**: Specify the schema (for example, **dbo**). * **Table Name**: Enter the name of the writeback table. * **Decimal Precision**: Specify the number of digits after the decimal point for numeric columns. {% hint style="info" %} **Important:** This is a one-time setting and cannot be changed later. {% endhint %} * **Text Length**: Define the maximum length for string columns (for example, **Length of all string columns = 512**) or choose **Custom**. After configuring all required fields, select **Add** to create the destination. 4\. After you configure all required fields, select **Add** to create the destination. The **SQL database destination** is now configured and ready for writeback.
5\. Select **Writeback** to write your data table. A notification shows the writeback status.
After completion, a confirmation message is displayed.
You can view the written data in the destination database.
6\. After the initial writeback, you may add or remove row or column dimensions as you build your report. If the destination structure must change because of these updates, a notification appears. You can drop and recreate the table with the updated structure before the next writeback.
### Logs Select **View Logs** to open the writeback log console. Logs include **milestones**, **payload size**, and **writeback duration**.
Select a writeback **ID** to view detailed information.
# General Settings General settings configure writeback behavior for a report. These settings control how data is structured, filtered, and written to the destination. ### 1. Writeback type Use **Writeback type** to define the table structure. There are four supported types.
#### 1.1 Long **Long** is the default writeback type. Each row represents an observation, and each column represents a variable. Only **Long** supports writing back **comments** and **notes**.
#### 1.2 Wide **Wide** stores measures as columns. As you add more measures, the writeback table adds more columns to accommodate them. Totals and subtotals are not written back in **Wide** format.
#### 1.3 Long with changes **Long with changes** uses delta writeback. Only changed values are written in the report. Previous values are stored in **PreviousValue**, and new values are stored in **Value**. The system also sets **IsLatest = 1** to identify the latest row. Delta writeback works for both numeric and text data type adjustments.
#### 1.4 Wide with changes **Wide with changes** uses delta writeback, but each measure is stored in a separate column. It maintains change history and uses **IsLatest** to identify the active record.
#### Writeback type considerations and best practices: 1. After the first writeback, changing the **Writeback type** displays a warning before existing tables are unselected in **Writeback > General Settings > Destinations**. You can reselect the table for writeback, but the system checks for conflicts between the existing table type and the current **Writeback type**. If a conflict exists, the system prompts you to drop and write back the table. When in doubt, create a new table and perform writeback instead of dropping an existing one. 2. Changing row or column dimensions displays a warning before it drops and writes back the table. ### 2. Filter Use **Filter** to control which data gets written back to the destination. The filter list includes predefined options and custom filters
#### 2.1 None **None** writes back the entire report or scenario without applying any filter. #### 2.2 Data with Comments only **Data with Comments only** writes back only the cells that contain comments. This option works only with the **Long** writeback type. #### 2.3 Calculated rows only **Calculated rows only** writes back only calculated rows, including any notes added to those rows. ### 3. Additional column configuration Use **Additional column configuration** to add date keys to the writeback table during writeback. This requires consent to access all datasets in the workspace. **Add Date Key** adds a **Date Key** column to the writeback table. For high-level planning scenarios such as revenue by year or month, the system appends the first day of the month or quarter to the date dimension. For example, if the report is set to Year-Month, the system writes **01-01-2025** for **Jan 2025**. **Add Date Key** works only when a time intelligence date hierarchy is used in the **Columns** field.
### 4. Decimal Precision Use **Decimal Precision** to define the number of digits after the decimal point. This is a one-time setting that applies to all destinations configured for a report page. When you configure the first writeback destination, the system displays a dialog to set decimal precision.
You can configure numeric data with up to 10 decimal digits. Any digits beyond 10 are rounded off.
The configured precision appears in the **Decimal Precision** section.
### 5. Text field length Use **Text field length** to control the number of characters written back for text fields. The default limit is **512 characters**. When you add the first destination, you can either keep the default limit or allow writeback up to the maximum supported by the backend. This is also a one-time setting for all database destinations on that report page.
The **Text Length** section of the report shows the set text length as follows.
{% hint style="warning" %} If the **Text length** exceeds the configured limit, **writeback fails**. {% endhint %} # Data Settings Under **Data settings**, you can view all series configurations and select the series that you want to include in **Writeback**. This is where you control what data gets written back to the destination. ### 1. Select series for writeback This section lists all series that are available for writeback. By default, all series are selected. You can clear the series that you do not want to **write back data** to the destination.
### 2. Show Text Inputs with HTML Formatting You can include your HTML content within the Text data input column as well as in DAX measures, which can be written back.
#### 2.1 Use HTML in a text data input column You can enter and render HTML directly within a text input column. 1. Double-click inside the text data input column. 2. Select the rich text editor icon to open the editor window. 3. In the editor, choose the Script option. 4. Enter your HTML code. 5. Select Apply. The HTML content is rendered and displayed within the visual. #### 2.2 Configure writeback behavior for HTML content When performing a writeback, you can control whether the HTML content is saved as plain text or with HTML tags. 1. Go to **Writeback> Settings > Data** in the Writeback Settings dialog. 2. Enable or disable the **Show Text Inputs with HTML Formatting** toggle. * When disabled, the content is written back as plain text. * When enabled, the content is written back with HTML tags preserved. #### 2.3 Use HTML in DAX measures You can embed HTML directly in a DAX measure. * Define a DAX measure that includes HTML code. * Drag the DAX measure to **Other (OM)** in the **Data** pane. * The HTML content is rendered in the visual. #### 2.4 Export HTML content When exporting data: * If the **Writeback as HTML** option is enabled, HTML content in both: * **Text input columns**, and * **DAX measures** is preserved and rendered correctly in outputs such as **PDF exports**. * If the option is disabled, the content is exported as **plain text** without HTML rendering. # Destination Settings Under **Destination settings**, you can add, view, delete, and manage writeback destinations. Use this tab to configure and manage existing destinations.
**Reset Writeback** If you change the writeback filter, you must reset writeback to clean up data that was written to the destination in previous writebacks. Reset removes both base data and scenario data that were written back earlier. # Configure advanced settings Under **Advanced settings**, you can control validation rules, and rename writeback columns. These options help you manage what data gets written back and how it is stored in the destination. ### 1. Validate writeback columns In the **Advanced** tab, you can enforce validation rules on data before it is written back. You can use a null check or a formula-based condition.
**1.1. Cannot be empty** Use **Cannot be empty** to write back only the cells that are not null for the selected field.
**1.2. Enter formula** Use **Enter formula** to define a formula that must evaluate to true for cells to be written back. Cells that do not meet the validation rule are excluded during writeback. You can also apply cross-filters. During writeback, the system shows a preview of excluded cells.
**1.3. Prevent writeback when validation fails** Turn on **Prevent writeback when validation fails** to stop writeback when empty fields are detected. When validation fails, the system generates an exception notification with details about the empty measures, columns, or rows that do not meet the validation condition.
### 2. Rename writeback columns You do not need to use the source dataset column names during writeback. You can specify a custom column name for the writeback table. To rename columns, go to **Writeback settings > Advanced** and select **Writeback column rename**.
{% hint style="info" %} Use standard database naming conventions, such as `snake_case` or `PascalCase`, to ensure compatibility with your destination database. {% endhint %} # Configure writeback destinations You can set up **Writeback** destinations for databases, data warehouses, data lakes, files, and webhook URLs. Use writeback destinations to save data using **Writeback** and load it to the **Writeback** table in the target system. Plan supports the following destination type: * Fabric SQL Go to **Writeback >** **Settings > Destination > Add Destination** to view and configure available destinations. ### Add destination Before you start writeback, add one or more destinations where your data table will be exported.
### Create a new connection In the **Create Destination** dialog, select **+ New** next to **Select a Connection** to create a new connection, or choose dropdown to select an existing connection.
### Configure the destination After selecting the connection, provide the required details: * **Database Name**: Select the target database. * **Select the destination database:** In the **Select destination database** dialog, browse and select the required database from the **OneLake catalog.** * For example, select **My\_Planning\_DB** from the list of available databases.
### Generic destination settings #### 1. Configure column settings You can define how numeric and text data is written back to the destination. * Set decimal precision * Use **Decimal Precision** to define the number of digits after the decimal point for numeric values. This setting applies to all destinations in the report. {% hint style="info" %} Plan supports up to 10 decimal digits. Values are rounded based on the configured precision during writeback. {% endhint %}
When you apply the decimal precision shown in the image above, Plan writes back values (in this example, to Fabric SQL) rounded off to 5 decimal places.
* Set text length * Use **Text Length** to control the maximum number of characters written back for text fields. By default, the limit is 512 characters, or you can allow the maximum supported by the destination.
# View Logs Plan provides writeback logging as soon as you start a writeback operation. You can review logs from **Logs** under the **Writeback** tab.
## 1. Filter writeback logs #### 1. Search logs Use the search bar to find logs by **ID**.
#### 2. Filter by status Select **Status** to filter logs by the execution state of the writeback operation. You can filter by **Success**, **Failed**, **Running**, and **Cancelled**. Multiple statuses can be selected simultaneously. * **Success** - If you select this option, the logs will be filtered to show only writeback processes that completed without errors. * **Failed** - Selecting this option will filter the logs to display writeback processes that encountered an error and did not complete successfully. * **Running** - This option will filter the logs to show writeback processes that are currently executing. * **Cancelled** - If you select this option, the logs will display writeback processes that were explicitly stopped or aborted before completion.
#### 3. Filter by time Select **Created At** to filter logs by when writeback started. You can filter by **Within the last**, **Last 7 days**, **Last 30 days**, and **Between**.
* **Within the last** - If you select this option, you can specify the number of hours, minutes, or seconds. The logs will be fetched if the writeback start time falls within this period. * **Last 7 days** - Selecting this option will filter the logs within the last 7 days * **Last 30 days** - This option will filter the logs that were created in the last 30 days. * **Between** - If you select this option, then you can specify the starting and ending date within which you can filter your writeback logs.
#### **4.Reset filter** Select **Reset Filter** to clear all applied filters.
## 2. Analyze writeback logs The writeback logs console displays a list of log columns that help you identify and analyze each writeback.
#### **1. ID** Displays the unique identifier for each writeback. You can sort this column in ascending or descending order. Selecting the **ID** opens a detailed summary of the writeback. The **General** tab includes the summary, and status.
The details pane provides the following information: * **Writeback Execution ID** - The unique system generated identifier for this specific writeback operation. * **Event source** - The specific action or trigger that initiated the operation (e.g., Writeback). * **Work Sheet Name** - The name of the report or worksheet from which the data originated. * **Scenario Name** - The specific planning scenario associated with the data being written (e.g., Base). * **Series/Measure** - The specific data points, calculations, or measures included in the writeback payload (e.g., Sum of Actuals, Sum of Plan). * **Incoming Cell Count** - The total number of individual data cells passed from the source to the writeback engine. * **Created At** - The exact date and timestamp when the writeback request was registered by the system. * **Started At** - The exact date and timestamp when the processing engine began executing the request. * **Status** - The overall outcome of the execution process (e.g., Success, Failed). * **Duration** - The total time elapsed from the start to the completion of the writeback operation. * **Writeback Filter** - The specific data filtering criteria applied to the payload before committing it to the destination (e.g., Calculated rows only). * **Writeback Type** - The structural format used to write the data to the destination (e.g., Long). * **Started By** - The user account that initiated the writeback process. * **Updated By** - The user account responsible for the most recent update to the process state. Select Fabric SQL to view the configuration, connection details, and execution outcome of the writeback operation specific to your Microsoft Fabric SQL database destination.
Select the specific destination table entry (e.g., Sales\_Plan\_Writeback\_12) to view detailed connection properties and execution metrics for that specific Microsoft Fabric SQL database target. The details pane provides the following information: * **Type** - Identifies the destination platform (Fabric SQL). * **Host** - The server endpoint URL used to establish the connection to your Fabric environment. * **Database Name** - The target database for the writeback execution. * **Schema** - The database schema containing the target table. * **Table Name** - The exact table designated to receive the writeback data. * **No. of rows** - The total number of rows successfully written to the destination table during this operation. * **Status** - Indicates whether the writeback operation to this specific destination succeeded or failed. #### 2.Duration Displays the total time taken to complete the writeback. #### 3. Status Indicates whether the writeback succeeded or failed. This column can be sorted alphabetically. #### 4. Created at Displays the date and time when the writeback was initiated. You can sort this column chronologically. #### 5. Started by Displays the user who started the writeback. You can sort this column alphabetically. #### 6. Scenarios Displays the scenarios included in the writeback. If no scenario was written back, the console shows **Base**. #### 7. Incoming Cell Count Displays the number of cells written back from the report. #### 8. Event Displays the type of log entry, such as Writeback or Reset. #### 9. Writeback Type Displays the format used for the writeback, such as Long. # Enable writeback in reading mode Plan lets users perform writeback even when a report is in reading view. You can also review logs from Logs under the Writeback tab.
{% hint style="info" %} The **Writeback t**ab option is available only when at least one destination is configured and the user has writeback permissions. {% endhint %} The following options are displayed for users in the reading view who also have writeback access:
### **1. Writeback** Select **Writeback** to write report data to the configured destination. Plan respects row-level security (**RLS**) in the source dataset. If a viewer has RLS applied, Plan writes back only the rows allowed by that security rule. ### **2. Logs** Select **Logs** to open the writeback logs page. # FAQs on Writeback **a. If a user updates a data input column in reading mode and closes the report, will the latest data be available when reopening?** Yes. Plan captures changes made to data input columns and stores them in the backend database. This works in both reading and edit modes. When the report is reopened, Plan retrieves the latest values and loads the report with updated data. **b. How does row-level security (RLS) work in Plan?** Plan respects the RLS defined in the original Power BI dataset. The Planning sheet displays only the rows that the user is allowed to see. RLS in Power BI applies to users with Viewer access in the workspace. **c. How does Writeback work with RLS?** Plan writes back only the rows visible to the user. If a user can see a limited set of dimension categories based on RLS, only those rows are written back. You can write back only the data visible in the Planning sheet. **d. How is data stored when multiple users perform Writeback? When are rows overwritten or appended?** Plan processes writeback operations sequentially. During writeback, Plan compares incoming rows with existing rows in the destination. If all dimension columns and values match, Plan overwrites the existing row. However, if you would prefer Plan not to overwrite rows during writeback commits, select Writeback only changes/Delta writeback as the writeback type. Under this mode, Plan retains previous values.
# Layout options Plan offers two major types of layout options - **Table layouts** and **Measure layouts**. Each of these comes with its own set of variations and can be used together in a single layout. The Table Layout lets you determine how categories in rows need to be displayed. The Measure Layouts give you flexibility specifically with respect to how measures are displayed. {% embed url="" %} Table and measure layouts - Overview {% endembed %} ## 1. Table layouts ### a. Hierarchy By default, Plan delivers data in a hierarchical, expand/collapse-enabled format. When you select the Layout item from the menu, you will notice the selection defaulted to 'Hierarchy'.

Hierarchy layout

Changing this selection helps you modify the report layouts to the other types listed below. ### b. Outline The outline layout shows the row categories in individual columns. By default, the 'ruler' is also enabled for this layout. The ruler is used to resize rows and columns easily, as you do in spreadsheets, and to select a specific column. You can hide the ruler by deselecting the ruler icon on the toolbar.

Outline layout

### c. Table The table option shows categories in a tabular format, without any expand-collapse hierarchies for rows. Note that the hierarchy expand/collapse option is available for columns (Year-Quarter-Month).

Table layout

### d. Stepped The stepped layout is similar to the outline layout in that each category is listed in a column. The difference is in the availability of a separate total row for each branch of the hierarchy.

Stepped layout

### e. Drilldown The drill-down layout lets you explore one branch at a time in the hierarchy. This is also an ideal option when you have a huge volume of rows.

Drilldown layout

### f. Tree The tree layout helps you to customize the structure and display of your data, also helps you to visualize absolute and relative variances between the compared measures. Go to **Home > Layout > Tree**. In the Configure Tree View pop-up select your baseline and Comparison measures and save.

Configure Tree View pop-up.

After configuring the Tree View, click Save. The Tree View Option and its ribbon are added to the toolbar, which helps you in customizing your Tree view. In the image below, we have COGS as the baseline compared with Gross Margin for all the quarters from the year 2023 to 2025.

Customized Tree View Layout

In the Tree View ribbon, we have various options to customise the tree layout. They are * Timeline: You can select the timeline ranges for comparison between the measures. * Quick Format: Lets you select the number scaling format. * Baseline: Select your baseline measure. * Comparison: Select the measure to be compared to the baseline. * KPI: Select your Key Performance Indicators Measures. * Create Scenario: You can easily create scenarios and simulate them. Click [Scenario ](/documentation/readme/planning-sheets/how-tos/7.-planning-budgeting-and-forecasting/scenario)to know more about it.

Scenario created in Tree Layout

* Compare Scenario: Various scenarios created can be compared. Click [Compare Scenarios](/documentation/readme/planning-sheets/how-tos/7.-planning-budgeting-and-forecasting/scenario/compare-scenarios) to know more about it. * Display Settings: Under Display Settings, we have options to customize the tree layout's display. * Node Customization: We can preview and customise the node scope, style, and components here.

Standard- Node Style

Compact- Node Style

Custom-Node Style

Under the Custom Node style, we can select the scope of the node * Global: The Custom settings are applied to the entire report. * Level: The custom settings are applied only to the particular level. * Node: The custom settings are applied only to the particular node. You can also do customisations by clicking on the pencil icon in the report. Each element in the tree layout can be customised to suit your needs. * Level configuration: Here, node-level customization can be done. * Appearance: Font color, main color, background color, and conditional formatting can be done.

Appearance- Display Settings

## 2. Measure layouts The default Measure layout is used in all the Table layouts listed above. You have two measure layout options. They are * In Rows. * In Columns. The image below shows the various measure layout options

Measure Layout options

### a. In rows When you change this to 'In rows', the measures are displayed as shown below.

Measure in rows

To re-order rows while using the Measure in rows layout, you can click on the row gripper and drag the rows to be re-ordered. Click on the gripper and drag and drop the measures as desired. You can view the hidden row dimension under the 'Manage Rows' options in your 'Measure on Rows layout'. You can unhide the row dimension by checking the box.

The hidden dimension is displayed under the Manage Rows in the Measure on Rows Layout.

### b. In columns When you change this to 'In columns', the measures are displayed above the column hierarchy categories.

Measure in columns

# Basic formatting Once you have chosen the layouts and/or templates, it is time to apply some basic formatting to the report. In this section, we cover the following: **1. Number formatting** - Plan comes prepackaged with *intelligent number formatting & scaling recognition* capabilities which will be covered in [Number formatting](broken://pages/c92iCVRNrnbNUiFTXFpS). **2. Cell & value formatting** - In Plan, you can change the font color, style, size, and text alignment in a cell, or apply formatting effects. To learn more, refer to [Cell & value formatting](/documentation/readme/planning-sheets/how-tos/basic-formatting/cell-header-and-value-formatting). **3. Totals & subtotals** - Be it a typical table or a hierarchical matrix style report in Fabric, Plan allows you to seamlessly manage subtotals and grand totals in a couple of clicks. To learn more, refer to [Totals & subtotals](broken://pages/PHuuwZ193YeUG8uTAoZ5). **4. Insert blank rows** - Financial statements and reports often require options to add white spaces for formatting. Plan provides the insert 'empty row' option to achieve this in a single click. To learn more, refer to [Insert blank rows](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-blank-rows). # Number formatting Plan for Power BI comes prepackaged with intelligent number formatting & scaling recognition capabilities that help display data in a perceptive manner in your table/matrix style reports. ## 1. Number Scaling Let us see how you can handle a dataset of varying granularities & formats using Plan. Go To **Home** > **Quick Format.** Here we have 4 options, let us look at each of them in detail.

Quick Format Options

### a. Uniform scaling The 'Uniform' option applies one fixed scale to the entire table and moves the scaling unit display to the header. This is useful when all the measures are of comparable magnitude in the same table.

Uniform Scaling

Plan automatically chooses the scaling based on the values. But you can change it if needed. When you expand the Uniform dropdown again, you can see more options from which you can select as needed.

Uniform Options Dropdown

### b. Measure level scaling By default, when you assign data to Plan, each measure is formatted using an individual scale. The scaling for each measure is shown in the column header. This is useful to show measures of varying granularities in the same table.

Measure level scaling

### c. Auto scaling When using the 'Auto' option, each cell is formatted individually. suffixes b, m, and k to denote billions, millions, and thousands. In the image below, you can see the - m which denotes millions.

Auto scaling

### d. Native scaling If you do not want any fancy formatting and need the numbers just as they are in the source data, select the ‘Native’ option.

Native scaling

## 2. Percentage, prefix/suffix, and decimals Plan provides options to fine-tune the format at a row, column/measure, or cell level using the icons just below the 'Quick format' dropdown. These options include conversion to % format, attaching prefix/suffix, and increasing/decreasing decimal spaces.

Number formatting options

### a) Percentage You can apply percentage number formatting at the cell level, Row Level or Column Level. To do this, let's select the column or row, or cell the % icon gets enabled. Once you click on the % icon, the values get converted to percentages.

% Number Formatting at Column Level

### b) Prefix/suffix Using Plan, you can insert prefixes such as currency or suffixes like units. In the example below, the price is $ per unit. Select the column, row, or cell and click on the highlighted $~~C~~ icon. Enter the prefix and suffix as shown and apply. Now the prefix and suffix are added.

Prefix and Suffix pop-up

Click Apply to see the changes.

$ prefixed and /unit suffixed

### c) Decimals You can increase or decrease decimal places at a cell/row/column level by selecting the cell/row/column and clicking on the 'Increase decimal' option to increase or 'Decrease decimal' icon to decrease the decimal places. In the image below, the decimal places of the Unit sold column are decreased, while those of the Revenue column are increased.

The Decimal increase and decimal decrease options are shown.

### 3. Semantic formatting Plan provides a number of options to customize numbers. In the 'Settings' tab, click on the 'Appearance' settings. In the 'Numbers' tab, you'll find options such as Semantic formatting, Value display, Sign, and more. In this section, let's talk about semantic formatting. You can see the positive and negative options on enabling the toggle, as shown below. By default, they are shown in green and red, but they can be customized using the color picker.

Semantic Formatting of Numbers

You can also display positive and negative numbers in the formats shown below. ![](/files/ehOjM0knkwWOvoQeYEa7) ![](/files/TIi1lbXzDifkBSU8xNJb)

Negative Numbers and Positive Number Formatting Options

# Cell, header & value formatting In **Plan**, you can customize the appearance of cell content by adjusting **font color**, **style**, **size**, and **text alignment**, as well as applying additional **formatting effects**. All of these options are conveniently accessible from the **Format** tab on the toolbar, within the **Font** and **Alignment** sections. This allows you to tailor the presentation of your data for improved readability and visual consistency.

Style and alignment sections

## 1. Style options Let's take a look at the style section. #### a) Font and size The default font is Inforiver Sans which has been specifically designed for use in financial and tabular reports. But you can change to a different font from the dropdown menu and change the default size. #### b) Increase/decrease font size Font size can also be changed by using the Increase/Decrease font size options. #### c) Font style Bold, Italics and Underline styles can be applied. #### d) Font/background color You can use the fill color option to apply background color to an entire column or row or even an individual cell. And use the font color option to change the foreground color or the font color of the data values, row or column headings, and subheadings.

Formatting options like Font Style, Size, Bold, Italic, Font Color, and Background have been set to column.

#### e) Format painter You can copy cell formatting using the 'Format painter' option just as in Excel. However, Plan goes one step further. If you have applied formatting to a specific hierarchy level, like the Category, you can instantly replicate that formatting across all other Categories using the **Apply Format to Level** option. In the example below, you can apply it to all the other Categories using the 'Apply format to level' option.

Apply format to level option

Format applied to level

#### f) Borders Plan provides several predefined border styles and they can be applied at a cell/row/column level. If the predefined cell borders do not meet your needs, you can create a custom border by clicking on the 'Custom' option.

Applying borders

To remove custom borders or predefined borders click on 'Reset' or select the 'No borders' option from the Borders icon (the 1st icon). ## 2. Alignment options Let's take a quick look at the alignment section in this video. #### a) Horizontal/vertical alignment Horizontal alignment can be formatted using the Align left, right, and center options. For vertical alignment, the options are Align top, bottom, and middle. #### b) Header orientation Column and measure header orientations can be changed using the highlighted options. This is very helpful when you have a lot of columns in your report.

Header orientation options

#### c) Increase/decrease indent Use the indentation icon from the toolbar to indent specific values. This feature makes creating financial statements a breeze. #### d) Row size You can increase or decrease row size manually. This is especially useful when inserting inline charts. #### e) Word wrap If your measure headers are very long, you can enable the word wrap option to ensure that the entire header is displayed. #### **f) Fit Content** You can choose the fit mode that is best suited for your data. we have options like * Fit to Header - Column size is set as per the Header. * Fit to Content - Column size is set based on the Content. * Best Fit - Auto fits the column size suitably * Manual Column Width - Customize your Column Width * Lock size - You can lock the column size.

Auto Fit options to set the Column Width

To know more, please refer to the [manage column width](broken://pages/EJcSslqWxvITS8B149Qg) section ## 3. Inserting icons in headers Enhance the visual appeal of your reports with icons and symbols in measures headers, column headers, and inserted rows. You can copy the preferred icon and paste it in the title section.

Inserting icons in headers

{% hint style="info" %} Icon can be inserted only in inserted measures, columns and rows. {% endhint %} ## 4. Auto-format for column dimensions When column hierarchies are involved, Plan allows you to select a particular level of the hierarchy by right clicking that level and choosing **Select column** from the context menu.

Select column option

This feature comes in handy when you need to apply formatting to a particular level of the hierarchy. Here, we have highlighted the Revenue Column of Q1 in 2024, which can be applied to all the columnA in the same hierarchy by Applying the Format to Level option

We've highlighted Q1 Revenue Column of 2024 in blue.

The image below shows the format applied to all the columns in that level

Formatting applied to all the columns in a level.

In the next section, we'll see how to handle [totals & subtotals](/documentation/readme/planning-sheets/how-tos/basic-formatting/total-and-subtotals). #### Resources [Best Fonts for Financial Reporting](https://inforiver.com/blog/general/best-fonts-financial-reporting/) # Total and Subtotals Consider the example below, we have hierarchies across both rows & columns.

Matrix with row and column hierarchies

Note that (a) The row subtotals are at the top, and (b) The row grand total is the top (c) There are no subtotals and grand totals for columns. ## 1. Totals The totals can be viewed under the 'Totals Tab' in the Home toolbar. There are options to enable/disable subtotals and totals, as well as to change their positions.

Options under Totals

{% hint style="info" %} Subtotals for rows and/or columns will appear only when a hierarchical structure exists within the respective fields. {% endhint %} ### 1.1 Row Grand Total and Subtotal You can choose to display the grand total of the rows or the subtotal of hierarchical rows either at the top of the rows or at the bottom of the rows by selecting the Top or Bottom option. You can even hide it by selecting the off option. In addition, row Subtotals can be split using the split option.

Row Totals at the Top and Subtotal at the Bottom

### 1.2 Split in Row Subtotal You select the split option to view the subtotal of each sub-section separately.

Row Subtotals are split

### 1.3 Column Grand Total and Subtotal You can choose to display the column grand totals or the subtotals either at the right end of the columns or the left end by selecting the 'Left' or 'Right' option. You can even choose to hide it by selecting the 'Hide' option.

Column Total to the Left and Subtotal to the Right

## 2. Freeze When **pagination is turned off**, you have the option to freeze totals. To turn off pagination, click on the settings icon at the bottom and select Rows per page as 'All'. {% hint style="info" %} It is not possible to freeze totals when using the 'Drilldown' layout. {% endhint %}

Enabling the Freeze option

Freeze 'On' option enables you to freeze the Grand Total either at the 'Top' or 'Bottom' of the Row.

Freeze 'On' with Row Grand Total at the bottom.

Plan allows you to manage row & column subtotals & grand totals the way you want in an intuitive & flexible manner. # Adding business logic and formulas In previous sections, we looked at the basic interactions and settings for displaying information available in Plan. In this section, we will go over how to quickly insert data input and calculated rows and columns. ## **Editing cells** Edit native measures and apply calculations at the work item level. Refer to [this section](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/editing-cells) to learn more. ## Rows * **Insert Data Input Rows:** The Planning Sheet allows you to insert static rows in matrix-style reports where you can input or enter the data. * **Insert Template Rows:** With a single click, you can automatically include template rows at each hierarchical level, eliminating the need to manually add them at each level. * **Insert Formula Rows:** The Planning Sheet allows you to insert calculated rows using an Excel-like formula engine. ## Measures and Columns * **Insert manual input columns:** Plan provides nine different data type options to manually enter data in your planning sheet, such as number, date, text, single/multi-select options, checkbox, person, last updated columns, etc. * **Insert calculated columns** - Within planning sheets, you can insert calculated columns or measures at the work-item level. * **Quick Formula:** This feature offers several one-click calculations for inserting both columns and rows. ## **Manage inserted rows & columns** Rows or columns inserted in the table, such as calculations, static rows, or data input columns can be managed using the 'Manage' options. To learn more, refer to [Manage inserted rows & columns.](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/manage-inserted-rows-and-columns) # Edit cells Plan allows you to edit the contents of a cell and apply calculations. These are visual level updates and will not impact the source dataset. ### 1. Overwriting values To overwrite an existing value, simply double-click on the cell and enter the new value. You can enter scaled values (250m, 5.6b, etc) or reference other cell values. Double-click on a cell as shown in the image. A formula bar is enabled below the toolbar. Type in a value and click 'Enter'. Note that Plan allows entry of scaled values (e.g. 45.3m instead of 45,300,000).

Overwriting a value

The value gets updated and the manual update can be identified by the pencil icon in the cell. The totals and subtotals are also updated to include the value entered.

Updated value

You can even reference other cells and apply formulas to them using the cell editor. You can define simple mathematical operations such as A +/- B, etc.

Using cell references and applying formulas

{% hint style="info" %} * Plan allows you to edit values AC, PY, PL, FC, and OM measures. You can edit dates and text measures as well. {% endhint %} ### 2. Apply formulas You can use the wide range of formulas and functions available in Plan while editing a cell. In the below example, we are using the 'Average' function. As you start typing, you can see a list of functions that match the entered text. Click on 'Average' to see more information, such as the arguments and examples.

Using formulas in static rows

b) You can click on any cell, and it gets automatically populated within the formula as shown below.

Selecting a cell

c) Let's calculate the average of the two selected cells.

Average of two selected cell

d) You can see the result of the operation, which gets updated in the totals and subtotals

Data captured

### 3. Updating multiple cells You can also override the data in multiple cells with the same value, for instance, set a fixed budget. Select the cells and update the value in any cell, the updated value will get cascaded to all the selected cells.

Updating multiple cells simultaneously

### 4. Renaming Row Categories You can also rename row categories by double-clicking on them. The same row category may be repeated at each hierarchy level for hierarchical datasets. When you try to rename such categories at one level, Plan displays a prompt to confirm whether the category should be renamed across all levels or only at that level.

Renaming Row Categories

### 5. Bulk edit In financial reporting, costs or revenues may need to be allocated across multiple fields, or during the budgeting/forecasting process, the same values may need to be distributed across different fields. In some cases, you may need to set the same date, such as the expiry date for a list of products, or assign the same manager to a group of locations. Let's look at the **Bulk Edit** feature that comes in handy in such situations. We will use an example where we need to bulk edit a data input-number column. Navigate to the Plan tab and click the Bulk Edit option from the Tools section. **STEP 1:** From the **Measure** dropdown**,** select the data input measure for which you want to perform a bulk update.

Select measure in Bulk Edit pop-up

**STEP 2:** You can use the Select filter to choose the dimension categories for which you need to update values. You can select multiple categories for each dimension, as well as add more dimensions by adding filter. As you make selections, you'll notice the report getting filtered dynamically.

Choosing dimensions by adding Filters

**STEP 3:** Select the row hierarchy level at which the value should be updated.

Selecting the row hierarchy category

**STEP 4:** Similarly**,** select the column hierarchy level at which the value should be updated. In this case, since we do not have a column-level hierarchy, we can either update at Grand Total level or Quarter level.

Selecting the column hierarchy category

**STEP 5:** Choose the type of update to perform and enter the value. If you choose **Append**, the value will be added to the existing value. If you choose **Set Value,** the set value will replace the existing value. To remove the existing value, choose **Reset Value.**

Type of update

**STEP 6:** You can choose to distribute the updated value to child rows based on the weights of another measure or distribute equally.

Distribute values

The cells are updated to 50m at the Segment level and distributed to the children based on the weights of the 2024 Actuals.

Bulk-edited cells are highlighted in yellow.

Similarly, we can bulk edit all other data input types, including text, date, person, single-select, and multi-select dropdowns. Specifically for date fields, you can either set a date from the date picker or add a duration in years, quarters, months, weeks, or days to the existing date.

Bulk Edit option for various Data input types is shown

{% hint style="info" %} When editing data in bulk: 1. If you are working with numerical data inputs, you can append and distribute values. 2. For date input type, you can specify the duration or choose a date. 3. When column headers have a hierarchy, like a region or date hierarchy, you can collectively apply changes to specific column levels. {% endhint %} {% hint style="info" %} Bulk editing is available for simulation measures and scenarios so that simulations can be applied to specific categories and dimensions in one go. Refer these sections to learn more: [Bulk editing a simulation measure](https://github.com/lumelinc/FabricPlanningDocs/blob/Dev/broken/pages/8hJdseABWtYfzORazz2Z/README.md#id-3.-bulk-editing-a-simulation-measure) and [Bulk editing a scenario](broken://pages/jUciLkXkARAxZhPl1Tqm#iv-bulk-editing-a-scenario). {% endhint %} # Rows Quite often, we would like to insert a row in our table/matrix reports in Plan and enter our own data. For example, a financial statement report connecting to a database query may be able to fetch revenue & expense metrics, but it might not retrieve the number of shares outstanding. Similarly, a sales report can leave out the sales data for a newly launched product category. To address such scenarios, Plan allows you to insert static rows, formula rows or templated rows in matrix-style reports where you can input the data. We have different types of rows that can be inserted. They are * [Data Input Rows](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-manual-input-rows) * [Formula Rows](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-calculated-rows) * [Forecast Rows](broken://pages/UgJIHYe8Jwiklm8fymAf) * [Blank Rows](broken://spaces/umzO5ksbJsNTwGdwPeQi/pages/gxazJQpGQs6lmYyUO1WW) We see them in detail in the following sections. {% hint style="info" %} [This section](broken://pages/UgJIHYe8Jwiklm8fymAf) provides a detailed explanation of Forecast Row. {% endhint %} # Insert data input rows Plan allows you to insert rows in matrix reports and enter data manually. In some scenarios, data retrieved from a source may be incomplete. For example, a financial statement report might include revenue and expense metrics but not the number of shares outstanding. Similarly, a sales report might not include data for a newly launched product category. To address such cases, you can insert data input rows in the report and enter the required data. In this article, you learn how to insert them and enter values directly in the report. ### Insert a data input row Insert a data input row using either of the following methods: * From the Planning tab * Using row gripper #### From the Planning tab 1. Select a row at the level where you want to insert the new row in the report. 2. Go to **Planning** > **Insert Row**. This option is disabled if no row is selected. 3. Navigate to **Data Input** and select **Number**.
#### Using the row gripper 1. Hover over a row to highlight the **row gripper** icon. 2. Select the gripper and choose **Insert** > **Data Input**.
A side panel opens as shown below, where you can configure the row properties.
Enter a name in the **Title** field and select **Create**. An empty row with default properties is added at the selected level. Before selecting **Create**, you can modify the default row properties by configuring additional settings. The following sections describe these settings. ### Data input row properties * **Row type**: Use the dropdown to select the type of row to insert: * **Calculated row (Formula)** * **Data input row (Number)** You can switch between row types at any time.
* **Insert As**: Choose how the row is inserted: * **Single Row** - Inserts one row. * **Templated** - Inserts the row across all hierarchy levels. For example, you can create a product line and replicate it across all region levels. * **Scaling factor**: Set the scale for values in the data input row to thousands, millions, billions, or trillions. By default, it is set to *Auto*.
* **Include in total**: When enabled, the row values are included in the parent total. It is enabled by default. * **Distribute parent value to children**: When enabled, row values entered at the parent level are distributed to child levels. * **Default value**: Set the initial value for the row, which can be a * static value, or * a value sourced from another row Choose **Static** to enter a static row value. Choose **Row** to source values from another row in the report. You can enter the required row name in the **Selected Row.**
* **Bind for cross filter/RLS**: Enable **Bind for Cross filter/RLS** to ensure that cross-filter selections and row-level security (RLS) rules are applied to formula rows and data input rows that reference other rows. [Learn more about binding rows](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-calculated-rows#configure-formula-row-properties).
{% hint style="warning" %} #### Note If the **Bind for cross filter/RLS option** is disabled, a manager responsible for *Canada* accounts may see a manually inserted row that references *US* data. {% endhint %} * **Delete a static row**: Hover over the row, select the row gripper, and then select **Delete Row**.\ Alternatively, you can delete rows from the [**Manage Rows**](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/manage-inserted-rows-and-columns) interface.
* **Allow Input**: You can choose when users are allowed to enter inputs into a data input row. * **Edit mode**: Users can enter values only in edit mode. * **Read mode**: Users can enter values even in read mode. ### Bulk insert data input rows You can bulk-insert leaf-level rows or row hierarchies using the **Insert Row(s)** option from the Planning tab or row gripper. #### Insert rows 1. Select any child row or the parent row under which you want to create new rows. 2. Use the row gripper and select **Insert > Insert Row(s)**, or go to **Planning > Insert Row > Data Input > Insert Row(s)**.
3. In the **Insert Row(s)** pop-up, you'll see that the parent row levels are already filled in. Enter the new row name. 4. To insert another row, select **Add New** or use the **+ icon** next to the parent category.
5. To insert multiple rows at once, use the dropdown next to **Add New** and choose whether you want to insert **1, 5,** or **10 rows**. 6. Enter row names and select **Save.** You can also specify the row dimensions where the new rows have to be added by selecting the icon in the **Insert Row(s)** window.
#### Insert a row hierarchy To insert a hierarchy of rows: 1. Go to **Insert Row(s)** and select **Add New**. 2. In the Insert Row(s) window, the parent row hierarchy is pre-filled with existing values. Overwrite these values to define the new row hierarchy. 3. Select **Save**.
Overwriting is allowed only when the row type is set to **Text** in **Insert Row >** **Manage Rows > Row Settings > Insert Row Configuration**.
### Allow blank values in categories While inserting row hierarchies manually, enable the **Allow Blank Values** toggle if you expect blank row categories in the leaf nodes. 1. Navigate to **Planning** > **Manage Rows** > **Row Settings** > **Insert Row Configuration**. 2. Enable **Allow Blank Values**.
By default, this toggle is disabled. The blank categories are highlighted in a red error box, and you cannot create rows with blank row categories. By enabling this option, you can insert row hierarchies that contain blank leaf categories. {% hint style="warning" %} #### Note You cannot create a blank parent node if the child nodes contain values. {% endhint %} ### Disable row insertion You can restrict users from creating new categories for a particular level. To restrict row creation: 1. Go to **Insert Row >** **Manage Rows >** **Row Settings** **>** **Insert Row Configuration >** **Manage**. 2. Set **Type** to **Disable Insert Row**. This prevents row insertion at the selected level and all levels above it, while child levels still allow insertion.
### Upload rows from Excel or CSV You can also upload row categories from an Excel/CSV sheet and map the respective columns in the configuration. 1. Go to **Insert Row > Manage Rows > Row Settings > Insert Row Configuration > Manage** and select **Options list from CSV.**
2. Select **Upload** and choose a CSV file from your system. 3. Preview the data and select **Add**.
4. Go to **Option Configuration** tab. 5. Map the column using **Filter options** by selecting **Add New**, then select **Save**.
6. The uploaded values appear in the **Insert Row** window. [Use standard insert steps](#insert-rows) to add them to the report. ### Insert distinct values as rows Select **Distinct Values** in the Insert Row configuration to work with unique values from a selected dimension.
When this option is selected, you can: * View only existing distinct (unique) values in the dropdown for the configured dimension in the **Insert Row(s)** window. * Select and insert values directly as rows or row hierarchy from the dropdown, avoiding manual entry. This helps streamline data entry by ensuring consistency and inserting rows within the existing row hierarchy by directly selecting the dimension value. # Insert template rows Plan enables efficient hierarchy handling with template rows, allowing you to insert custom rows across all levels of a hierarchy at once, instead of adding them individually. In this article, you learn how to insert template rows and use them across hierarchy levels. ### Create a template row 1. Select the row above which you want to insert the template row. 2. Go to **Planning > Insert Row > Data Input** and select **Number**, or select the **row gripper,** then choose **Insert > Data Input.** 3. In the **Static Row** panel, select **Templated**, enter a title, and configure values as needed:
4. You can configure a default value for the template row. * Choose **Row** in **Default Value** to source values from another row. * Choose **Static** to enter a fixed value. * Or enter values directly in the rows after creation. 5. Finally, select **Create** to insert the row across all hierarchy levels. {% hint style="info" %} #### Note You can also create formula (calculated) rows as template rows by selecting **Planning** > **Insert Row** > **Formula**. Alternatively, select the **Row Type** to *Formula* in the side panel above. {% endhint %} ### Configure template row properties Configure other properties for template rows, such as **Scaling Factor**, **Include in total**, **Distribute parent value to children**, and **Allow input**. For more information, see [row properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-manual-input-rows#data-input-row-properties). In addition to these, you can configure the following settings for template rows: * **Row position**: Defines where the row appears at each category level. By default, it is set to *Auto.* * **Auto** - Creates the template row above the selected row. * **First** - Creates the template row at the top of each category level. * **Last** - Creates the template row at the bottom of each category level.
### **Edit or delete a template row** * Use the row gripper and select the Edit or Delete Row options. * Or go to **Insert Row > Manage Rows > Template Rows**, hover over the created row, and choose the appropriate action through the icons.
### Conditional template rows You can control which parent categories and levels receive template rows by configuring template conditions. Template rows are created only for row categories that meet these conditions. 1. In the **Static Row** window > **Template conditions** section, select **Configure**. 2. In the **Set Template Conditions** window, specify where the template rows should be applied by choosing the required categories, and select **Apply**.
3. Use this to restrict or allow specific hierarchy levels to obtain the template rows. The following image shows the *Energy Drinks* template row inserted only for the selected category, *Beverages*, across the hierarchy.
### Dynamic referencing in template rows Plan provides a dynamic referencing feature that allows you to reference a sibling’s child row while inserting calculated template rows. This enables values to be automatically populated based on the corresponding parent category. #### Create a dynamic reference in template rows 1. Select the row above which you want to insert the template row. 2. Go to **Planning > Insert Row** and select **Formula**, or use the **row gripper > Insert > Formula.** 3. In the **Calculated Row** window, select **Templated** and enter a title. 4. Define a formula using **References** to source values from a sibling row. In the example below, a template row named *Cocktails* references the value of *Soda* under the *Beverages* category.
The referenced value is applied across all sibling categories within the same *Sub Region*. For sub-regions such as *APAC* and *EMEA*, the *Cocktails* row dynamically retrieves the value of the *Soda* subcategory within each respective sub-region.
# Insert formula rows In your planning sheet, you may occasionally need to perform calculations with row values. Formula rows let you calculate values based on other rows in the report. Define formulas by referencing existing rows and applying functions. The planning sheet has an intuitive formula editor where you enter the row formula. The Excel-like engine supports multiple functions (logical, mathematical, and more) and provides features such as autocomplete, syntax help, and multi-line editing to simplify formula creation and troubleshooting. {% hint style="info" %} For more information, see [formula syntax](/documentation/readme/planning-sheets/formula-syntax). {% endhint %} In this article, you learn how to insert formula rows and configure their properties. ### Insert a formula row 1. Select the row above which you want to insert a new row. 2. Go to **Planning** > **Insert Row** and then select **Formula**, or select the row gripper and select **Insert** > **Formula**. 3. In the **Calculated Row** panel, enter a title and define the formula, then select **Create** to insert the row.
{% hint style="warning" %} #### Note The **Create** option is enabled only after a valid formula is entered. {% endhint %} You can also create parent rows as calculated rows. To edit or further configure the calculated row, select the edit icon on the row.
### Formula editor The formula editor provides features to help you create and manage formulas efficiently: * **Functions** tab: View the list of available functions. * **Autocomplete (IntelliSense)**: Enable the **Suggestions** toggle to see function and reference suggestions as you type.
* **Syntax help**: View function syntax, arguments, and examples for better understandability and quick reference.
* **References**: Insert references to existing rows using any of these options: * Select a row directly from the report while the cursor is in the formula editor. * Use the **References** tab to search and select values based on hierarchy.
* **Expanded editor**: Use the expand option to open the **Maximized Formula View** with line numbers and detailed error messages for easier debugging.
### Configure formula row properties Common properties of calculated row can be configured by specifying **Row Type**, **Insert As**, **Scaling Factor**, and **Include in total**. For more information, see [row properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-manual-input-rows#data-input-row-properties). In addition to these, you can configure the following settings for calculated rows: * **Evaluated Formula For**: When a formula row intersects with a formula column, you can control how the column formula is applied. * **All Data Source and Input Columns:** Applies the formula to all relevant columns, including measures assigned to Values (AC), data input columns, and forecast columns. * **All Columns:** Applies the formula across all columns in the report, regardless of the type. * **Custom:** Allows you to selectively include or exclude column formulas. To configure, select **Custom** > **Configure** > **Include**/**Exclude** > **Save.** In the following example, the *Plan - ACME* column is included for the *Packaged Water* row.
* **Bind for cross filter/RLS**: Enable this option to ensure that cross-filter selections and row-level security (RLS) rules are applied to calculated rows. * **Bind using a row**: Select **Selection Type** as **Row** and choose a reference row to restrict visibility based on its data. In the following example, the *Mocktails* row references the *Juices* row. After binding, the *Mocktails* row is visible only to users with access to *Juices* data.
* **Bind using a dimension member**: Select **Selection Type** as **Dimension Member** and choose a dimension to control access. In the following example, the *Baked Items* category is bound to the *Beverages* category. As a result, *Baked Items* is visible only to users with access to *Beverages.*
* **Adding a description:** Optionally add a description to provide context for the formula. {% hint style="warning" %} #### Note The **Custom** option in **Evaluated Formula For** is enabled only when applicable columns or measures are available for evaluation. {% endhint %} ### View and manage formulas * Select a cell in the calculated row to preview the applied formula in the formula bar. * Use the **row gripper** to edit or delete the row as needed. * Or go to **Insert Row > Manage Rows > Rows**, hover over the created row, and choose the appropriate action through icons.
# Insert blank rows In reports and financial statements, you may need to add blank rows to improve readability and formatting. Plan allows you to insert blank rows in table or matrix reports to create visual spacing for better readability and presentation. In this article, you learn how to insert and remove blank rows. ### Insert a blank row 1. Select the row above which you want to insert a blank row. 2. Hover over the row and select the row gripper. 3. Select **Insert** > **Add Empty Row**.
A blank row will be inserted above the selected row. Blank rows can be inserted at any level of the hierarchy. ### Delete a blank row 1. Hover over the blank row and select the row gripper. 2. Select **Remove Empty Row**.
# Insert forecast rows Create row-level forecasts to predict outcomes at a detailed level, such as by region or product line. After creating a forecast measure, you can generate forecasts at the row level by inserting a forecast row. {% hint style="warning" %} #### Note The **Forecast** row option is available only when the report contains forecast measures. For more information, see [forecast measures](/documentation/readme/planning-sheets/how-tos/forecast-data-to-predict-future-trends). {% endhint %} In this article, you learn how to create and configure forecast rows. ### Insert a forecast row 1. Select the row where you want to create the forecast. 2. Go to **Planning** > **Insert Row** > **Forecast**, or use the **row gripper** > **Insert** > **Forecast Row.**
3. In the Forecast configuration window, enter a row name and [configure](#configure-forecast-row-properties) the required options. 4. Select **Save** to generate the forecast row. ### Configure forecast row properties You can configure the following options while creating a forecast row: * **Row name**: Specifies the label for the forecast row. * **Insert As**: * **Single Row** - Inserts a single forecast row. * **Templated Row** - Replicates the forecast row across all hierarchy levels.
* **Closed period**: Populate past or closed periods by: * Referencing another row: Select **Linked Row** in **Closed Period** and choose the row you want to refer. * Defining a formula: Select **Formula** in **Closed Period** and define the formula.
* **Open period**: Populate future periods using one of the following: * **Linked row:** Reference values from another row. * **Formula:** Define a formula for forecast values. * **Data input:** Manually enter forecast values with optional default values using a **static** value, another **row**, or a **formula.**
{% hint style="info" %} Select the **Templated** option when the row categories repeat across all levels of the hierarchy. {% endhint %} ### Configure forecast range The forecast time frame is configured when you[ create a forecast measure](/documentation/readme/planning-sheets/how-tos/forecast-data-to-predict-future-trends). After configuring forecast row properties, select **Next** to define the forecast time range in **Target Periods**. {% hint style="info" %} The **Next** option to configure the period range is available only when **Data input** is selected for the **Open period**. {% endhint %} You can configure the forecast range using one of the following options: * Apply a single configuration for the entire period.
* Split the forecast into multiple time ranges using **Add Range.**
When splitting the forecast period, ensure that all time ranges together cover the entire forecasting duration. Otherwise, the forecast cannot be created. ### Configure forecast source * **Set source**: Choose how to populate forecast values: * Use a blank forecast (manual input) * Reference another row
* **Source row**: Select the row whose values will be used when sourcing by row.
* **Operation**: Define how forecast values are calculated: * **Period range** - Copy values from a selected time range. * **Single period** - Use values from a specific period. * **Average of period range** - Use the average of selected periods.
### Edit and update a forecast row You can edit an existing forecast row by selecting the edit icon on the row. The side panel opens as shown below, where you can update the following properties:
* Edit the **Title** as needed. * Re-configure **Period Settings** to update forecast values for closed and open periods. * Configure **Scaling Factor**, **Bind for Cross filter/RLS**, **Include in total**, **Allow Input** and **Description.** For more information, see [data input row properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-manual-input-rows#data-input-row-properties). After making the required changes, select **Update**.
# Measures and Columns With Plan, it is possible to insert a new calculated row, column or measure at the visual level in your Power BI table/matrix style reports, without writing DAX. The rows, measures and columns so created can also be formatted, rearranged, and utilized for downstream calculations. The Excel-like formula engine supports 50+ functions (logical, boolean, math functions and more). The formula editor provides syntax, examples and features such as autocomplete, multi-line support and more to help users create, and troubleshoot formulas. {% stepper %} {% step %} ### [Data Input Columns and Measures](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns) Enter your data in your Microsoft Fabric report {% endstep %} {% step %} ### [Formula Columns and Measures](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-calculated-columns) Create calculated column or measure at the visual level without writing DAX. {% endstep %} {% endstepper %} # Insert data input columns Plan allows you to insert calculated rows, columns, and measures directly in table or matrix reports without writing DAX. These can be formatted, rearranged, and used for further analysis. The built-in formula engine supports a wide range of functions and provides features such as autocomplete, syntax help, and multi-line editing to simplify formula creation. In this article, you learn how to insert and configure data input columns and measures. ### Data input types Plan provides multiple options to manually enter data in your Microsoft Fabric report. You can enter data in single or multiple cells, and format it as needed based on your reporting requirements. The following table lists the available data input types:
Input typeDescription
NumberDouble-click and start typing values in directly into a cell
TextEnter text with multi-line support and word wrap
DropdownUse the available presets or create your own list of values; supports both single-select and multi-select
DateAdd a date from the calendar/date picker
CheckboxUsed in case of binary selections
PersonInput users from your organization
### Create a data input column To add a data input column to your report: 1. Go to **Planning** > **Insert Column.** 2. Select the required data input column type.
3. Configure the column properties and select **Create.** ### Configure data input column properties You can configure the following properties while creating a data input column: * **Insert As**: Choose how the column is added: * **Visual measure** - Inserts the input column across each category when a column hierarchy exists. * **Visual column** - Inserts a single column regardless of hierarchy.
* **Allow input**: Control when users can enter data: * In both read and edit modes (default).
* Only in edit mode.
* Based on a formula where input is allowed only when a condition is met.
* **Input type**: Change the type of data input column before creating it.
{% hint style="warning" %} #### Note The **Insert as** and **Input type** settings cannot be changed after the column or measure is created. {% endhint %} * **Aggregation**: Defines how totals and subtotals are calculated. By default, values are aggregated using **Sum**, but you can choose other methods such as average, minimum, or maximum.
* **Distribute parent value to children**: When enabled, values entered at the parent level are automatically distributed to child rows. This is useful for budgeting and allocation scenarios. {% hint style="warning" %} #### Note The **Distribute parent value to children** feature is supported only for **Sum** and **Weighted Average** aggregation types. {% endhint %} * **Default value**: Display a predefined value in empty cells. This can be a static value, a measure, or a formula. If the underlying measure or formula changes, the default value updates accordingly. {% hint style="warning" %} #### Note For visual columns, default values can only be set using **static values**. {% endhint %} * **Minimum and maximum values**: Set thresholds to control the allowed input range for leaf-level cells. These can be defined using a static value or a measure. If a value outside the defined range is entered, an error message is displayed.
* **Description**: Add a note for reference. ### Access control Plan allows you to control read and write access for data input columns. To configure access: 1. Select **Security** to open the access control window.
2. Locate the required column or measure. 3. Add users by searching for their name or email under: * **Read Access** to provide view-only access. * **Read + Write Access** to allow editing. {% hint style="info" %} #### Notes * Multiple users can enter data in the same published report, including in reading view. You can track all changes using the audit module. * By default, users can view data entered by others. When row-level security (RLS) is enabled, users can only see the data they are authorized to access. * No additional setup is required to enable data entry. You can use the data input feature to add input fields of various types. * You can write back data to external destinations such as Fabric SQL. You can also export the data to formats such as PDF or Excel. {% endhint %} # Number Plan allows you to enter and format numeric data in multiple ways. You can create a new empty series or copy values from another series. In this article, you learn how to create and manage numeric data input columns. ### Create a data input number column To create a data input number column: 1. Go to **Planning** > **Insert Column** and select **Number.** 2. Choose one of the following options: * **Insert a new empty series** * **Copy from another series**
### Create a blank number column The **Insert a new empty series** option inserts a blank numeric column that you can configure and populate through manual input. After selecting this option, a side panel opens as shown below.
Enter a title and configure the required properties to create the column. For more information, see [configure data input column properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns#configure-data-input-column-properties). After configuring the properties, select **Create**. The column is then inserted into the report.
### Enter values You can enter values in the following ways: * Double-click a cell and enter values using the formula bar.
* Enter values directly in the cell by selecting the cell and hit enter after entering value.
Entered values are automatically aggregated to parent levels and distributed to child levels when applicable.
### Create a number column from an existing series This option creates a numeric column by copying values from an existing series. * You can select from available measures, forecasts, or even hidden measures. * The copied values act as initial values only. * Changes to the source series are not reflected in the new column. To create a numeric input column using values from an existing series: 1. Go to **Planning** > **Insert Columns** > **Number** 2. Select the **Copy from another series** option. 3. Choose the measure or column whose data you want to use.
4. After selecting the source series, a side panel opens for configuration. Update the title if required and select **Create**. The column is created with pre-populated values.
### Modify column properties To modify an existing data input column: 1. Go to **Planning** > **Insert Column** > **Manage Measures.** 2. Select the required option: * Edit (pencil icon) to modify the column * Delete to remove the column * Show/Hide to control column visibility
Alternatively, you can use the column gripper: 1. Hover over the column or measure header to display the column gripper. 2. Select the column gripper and choose the required action, such as **Edit Measure**, **Delete Measure**, or **Hide Column.**
3. If you select **Edit** option, a side panel opens where you can update the required properties. After making the changes, select **Update**. ### Create a data input column from an existing column You can create a new data input column by copying values from an existing column. To copy as data input: 1. Hover over the required column and select the column gripper. 2. Choose **Insert** > **Copy as data input.**
3. A side panel opens where you can update the title and configure properties if required. Select **Create**. The new column is created with copied values. Changes to the source column are not reflected in the new column. # Dropdown Plan allows you to create single-select or multi-select dropdown columns using predefined lists of values. * **Single select** allows only one value to be selected. * **Multi-select** allows multiple values to be selected. * Presets are available only for single-select columns. ### Create a dropdown column To insert a dropdown column: 1. Go to **Planning** > **Insert Column** > **List.** 2. Select **Single Select** or **Multi-select.**
### Create a list of values After selecting **Single Select** or **Multi-select**, a side panel opens where you can define the list of values (LOVs) using **List** or **Presets**.
#### List 1. Selecting **Options** > **List** displays three default options with colors. 2. You can rename, add, or [modify these options](#modify-list-of-values). 3. Select **Create** to apply the list.
#### Presets 1. Selecting **Presets** opens a list of predefined value sets. 2. Hover to preview the values. 3. Select a preset and choose **Apply.**
### Modify list of values You can modify an existing dropdown column using the same steps as other data input columns. For more information, see [modify column](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/insert-manual-input-columns#modify-column-properties) *properties*. 1. Go to **Manage measures** and select the **Edit** (pencil) icon. 2. Alternatively, use the column gripper of the dropdown column and select **Edit Measure.** 3. Selecting **Edit** opens a side panel where you can update the required properties and perform the following actions: * **Edit options**: Modify option names by typing over existing values or change colors using the color picker.
* **Add options**: Select **Add option**, enter a value, and press Enter. * **Delete or reorder options**: Use the available icons.
4. After making the changes, select **Update**. The Update button is enabled only after changes are made. ### Localize list of values Plan allows you to display dropdown values in different languages based on user preferences, which is useful for global teams. To configure localization: 1. Go to **Format** > **Translations.** 2. Add translation entries for the required keys and languages using the **Add New** option or the **+** icon by hovering over a row. * Alternatively, you can import an Excel file in the same format available in the **Translate** tab. 3. Select **Save** to apply the changes.
4. In the **Single Select** data input column, use the *GETLOCALELABEL* function in the **Options** field and pass the key defined in the localization settings. When the report language changes, the dropdown values are automatically displayed in the selected language. #### Example In the example below, the dropdown options are displayed in French based on the current language settings.
### Configure dropdown properties Common Properties such as **Insert as**, **Allow Input**, **Default Value** and **Description** can be configured. For more information, see [configure data input column properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns#configure-data-input-column-properties). In addition, you can configure the following properties for dropdown columns: * **Options style**: Choose how dropdown values are displayed - **Chip**, **Arrow** or **Plain text.**
* **Icon position**: Choose the desired icons and control where the icon appears in the option: * Display on the left or right * Not applicable for **Chip** style
* **Allow users to add new options**: When enabled, users can create new options directly from the dropdown without opening the configuration panel.
* **Entry in total and subtotal rows**: Enabled by default. Disable **Allow entry on Total/Subtotals** to restrict input in total and subtotal rows.
* **Default value**: Define a default selection to avoid empty values: * **Static** - Select a predefined value from the dropdown values.
* **Dimension** - Use a dimension category.
* **Measure** - Use a measure. {% hint style="info" %} #### Note When using **Dimension** or **Measure** as the **Default Value**, enable **Allow user to add new option.** {% endhint %} ### Use the dropdown After configuring the column and list of values: 1. Select **Create.** 2. Click a cell in the column to open the dropdown. 3. Choose the required value.
For **Multi-select**, you can select multiple values in a single cell.
# Dropdown options from semantic models For the Single select and Multi-select columns, a list of values (LOV) can be created from your Power BI semantic models or other dimensions like Master Data reference fields. The options are dynamically updated as the source data changes. ### 1. Prerequisites In order to source dropdown options from published semantic models, users need to be able to query the semantic models using Power BI REST APIs. In the Power BI admin portal, enable the **Semantic Model Execute Queries REST API** toggle. Choose the **Apply to - The entire organization** radio button.

Power BI Admin portal settings

### 2. Creating a LOV from a semantic model Let's create a LOV from the dataset for a multi-select column. We'll create a multi-select field to assign a subregion. **STEP 1:** In the multi-select column side panel, click on Semantic model to open the **Add options from semantic model** dialog box.

Semantic model option

**STEP 2:** Select the Power BI workspace from the 'Workspace' dropdown. You can also search for a specific workspace.

Select the workspace

**STEP 3:** Select the semantic model and the table from the respective dropdowns. Click Next.

Select the semantic model

Select the table - Acme Region Master

**STEP 4:** From the **Label Column** dropdown, you can choose the column from the table (selected in Step 3) which will be used to populate the multi-select options. You can associate the options with an ID by selecting the **ID Column**. For example, if the multi-select shows a list of product names, you can tag them to a product ID field (if available in the data model). The multi-select options will not be impacted even if the product name changes, as the Product ID field remains constant. {% hint style="info" %} If an ID field is not available in your dataset, use the Label Column as the ID column. {% endhint %}

Selecting the column to source the multi-select options

You can assign regions from the multi-select dropdown after completing the steps discussed above.

Multi-select dropdown sourced from the dataset

**STEP 5:** After configuring and creating the dropdown, to preview all the options sourced from the semantic model, click the icon. If you need to change the configuration, click the icon to open the semantic model configuration window.

Previewing options

### 3. Using joins and filters #### Joins The base data in our visual may be from one table, but the options in the dropdown can be from a different table. The join option comes in handy in these scenarios. If you need to join your base table with another table to source dropdown options, you need to be mindful that a common column connects the two tables. In this case, the Retail-Orders and Retail-People tables are connected by Region.

Joining tables

#### Filters There are many scenarios wherein the options in the dropdown need to change based on the row dimension category. For example, consider that we have regional data in our rows. We need to use a dropdown to assign a manager for each region. The person we assign must also be tagged to that specific region i.e. the dropdown options for the Central region should only show the people under that region. We can use filters in such scenarios. Let's look at a scenario where we need to use joins and filters. Please note that the filter and join features can be used independently as well. **STEP 1:** Select the workspace, semantic model, and table as discussed in the earlier section. **STEP 2:** You can fetch the data from another table by checking the **Join Table** checkbox. After ticking the checkbox, you will be able to specify the table to join with. The base table used to populate rows and columns in the report is Retail - Orders. We need to fetch the person data from the Retail - People table. The Retail -Orders and Retail-People are connected by the Region field (foreign key).

Joining Retail-Orders with Retail-People

**STEP 3:** Let's assign the field to source the multi-select options - in this case, Retail - People.Person. Since the Retail - People table does not have a unique ID column for each person, let's use the Person field as the option and the ID.

Soucing options from the Person in the dataset

**STEP 4:** We need to filter the people based on the region they are assigned to. In the **Columns** dropdown**,** select the field based on which you need to filter the options. In this case, it is the Retail - People.Region which can be used to identify the people tagged to a region. Select the matching field from the visual from the **Visual Column** dropdown.

Setting a filter for dropdown options

The dropdown options for people data have now been fetched from the Retail - People table. Notice how all the people are displayed in the options for the grand total row. For each region, only the person assigned to that region is shown in the dropdown.

Filtering options dynamically

#### Data validation check for copy-paste in dropdowns When dropdown filters are enabled, Inforiver also checks if the data is valid when you copy-paste data manually within a dropdown data input column. Note that the Copy options available in the report (Copy to all rows, Copy until last row etc.) are disabled when dropdown filters are applied. If you accidentally try to paste an invalid option, you will be notified that the copied cell values do not belong to the chosen category. In the following example, we tried to copy the dropdown cell values (Bookcases, Binders) from the categories *Furniture* and *Office Supplies*, and paste them into rows belonging to different categories (Bookcases -> Office Supplies and Binders -> Technology). Inforiver prevents this type of copy-pasting, as the data is not valid for the respective categories. You will see a toast message indicating that the operation is not allowed.

Data validation check in dropdowns with filters

### 4. Refreshing data The underlying data in the semantic model is likely to change - there may be new data added or updates to the existing data. Any updates to the underlying data should be reflected in the dropdown options too. * You can schedule an automatic refresh at a specific time on a daily/monthly basis. * You can also run ad-hoc data refreshes to sync the dropdown options with the semantic model.

Refresh dataset option

#### Triggering ad-hoc refreshes You need to check the **Require Semantic model Refresh** option to trigger manual refreshes. Click the icon in the Options section to trigger a refresh from within the Inforiver visual.

Manual refreshes

Inforiver also provides an **API endpoint** to manually sync the semantic model from outside the visual. The API endpoint is provided in the Refresh Link section. [Learn more about working with API endpoints and authorization tokens.](broken://pages/dlIe2yqUo6wWj9uu2rOE)

API refresh option

Click on the icon to view the semantic model refresh history. Any errors that occur during the refresh can be viewed from this screen.

Refresh history portal

# Lookup visuals for dropdowns The Inforiver Lookup visual has been designed to help you seamlessly integrate data from different semantic models into your reports and ensure that the data is up-to-date and accurate. #### 1. Top 3 benefits of using the Inforiver Lookup visual * You can fetch data from your source tables in real-time, provided you have an up-to-date semantic model or the lookup table is in direct query/lake mode. * Row-level security is implicitly handled as the helper visual displays the list of values based on the data Power BI transmits for a particular user. * The lookup visual is lightweight and requires virtually no maintenance like data refreshes. #### 2. Importing and configuring the Lookup visual The Inforiver Lookup visual works in tandem with the Matrix visuals. It can be downloaded from our [customer portal](https://inforiver.com/login/) for free and then [imported](https://docs.inforiver.com/introduction-to-inforiver/get-started/installing-inforiver-for-yourself) into Power BI.

Download the ookup visual

To configure the visual, simply drag the dimension with dropdown options from your semantic model into the Dimension field. You can add multiple lookup visuals for a single Inforiver Matrix visual.

Adding lookup visuals and assigning dimensions

#### 3. Sourcing single-select and multi-select options from the Lookup visual The lookup visual is a more efficient solution that overcomes the drawbacks of connecting directly to the semantic model from Inforiver – the data is refreshed automatically when the Power BI semantic model is refreshed, and RLS settings are honored. #### 4. Creating a dropdown with Lookup visuals **STEP 1:** Select the **Lookup Visual** option and specify the lookup visual name When you choose the Lookup Visual option, you must specify the visual name in the configuration. In this case, we are populating dropdown options for the manager’s name – so we’ve selected the corresponding visual name.

Select the lookup visual name

**STEP 2:** Assign the dimension field to source dropdown options Next, select the **Column Label** field – the options in the dropdown will be sourced from this field. If you have a primary key or ID field associated with the label field, you can assign the ID field to the **Column ID**. If the column labels are updated, the ID field mapping ensures that the updated value will be reflected in your reports. Note: If your lookup table has no ID field, use the same label field (in this example, Manager Name) as Column ID.

Assigning the Label and ID

**STEP 3:** Click Add to apply the configuration and create the dropdown. #### 4. Ensuring data integrity for dropdown options Let’s take a closer look at assigning a manager from the dropdown. Suppose a manager has jurisdiction over the *South* and *East* regions only – but he is incorrectly assigned as an approver for a product category in the *Central* region. A manual error such as this one in selecting options could result in ambiguity, delays, and possible financial losses. When you deal with huge data volumes, such operational errors are inevitable. With Inforiver, you can enforce data integrity by specifying filters while configuring dropdowns. Please be aware that to use filters, the dimension (in this case, Region) needs to be added to the Inforiver and Lookup visuals.

Creating filters

Let’s look at the dropdown in action. With filters applied, notice how the options change dynamically based on the region.

Options for Central

Options for East

#### **5. Automatic cross-filtering** The lookup visual has the added capability to cross-filter data. The sample report shown below sources product data from a lookup visual. Notice how the product names change dynamically based on the product sub-category. {% hint style="info" %} * Power BI **Edit interactions** toggle needs to be enabled for cross-filtering to work correctly. {% endhint %}

Cross filtering

Disable the cross-filter option to pull all the data from the lookup visual, irrespective of the row dimension category in the matrix visual.

Disabling the cross filter

# Text, checkbox & date In addition to [number](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/insert-manual-input-columns) and [dropdown](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/dropdown) input columns, Plan allows you to capture manual input using **text**, **checkbox**, and **date** columns. ### Insert a text column You can use text columns to capture free-form input in reports. To insert a text column: 1. Go to **Planning** > **Insert Column** > **Text.**
2. Enter a title and configure the required settings in the side panel that opens when you select **Text**, as shown below.
3. Select **Create.** After creating the column, double-click a cell to enter text and press enter to save. You can [modify a text column](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/insert-manual-input-columns#modify-column-properties) to edit its configuration.
### Configure text input column properties Key properties such as **Insert As**, **Input type**, **Default Value**, **Allow entry on Totals/Subtotals**, **Allow Input** and **Description** can be configured as in other data input columns. For more information, see [configure data input column properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns#configure-data-input-column-properties) and [configure dropdown properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/dropdown#configure-dropdown-properties). In addition, text columns support the following properties: * **Word wrap** - Enable word wrap for long text values to improve readability. Use the **word wrap** option in the **Format** tab to adjust text based on column width. * **Text validation** - Validate text input to ensure data quality. You can: * Define **minimum** and **maximum length** to control the length of text input.
* Restrict input type (numeric, email, alphanumeric) by selecting the required option from the **Field Validation** dropdown.
* Use the **Custom** option to apply a regular expression (regex). Select **Custom** and enter the required text pattern.
{% hint style="warning" %} Only text that satisfies the validation rules is accepted. Invalid entries display an error. {% endhint %} | Field Validation | Allowed Text | | ----------------- | ----------------------------------------------------------- | | **Any Value** | Allows numbers, alphabets, punctuation, and special symbols | | **Numeric** | Only numbers | | **Non Numeric** | Everything except numbers | | **Alpha Numeric** | Numbers and alphabets | | **Email** | Valid email addresses | | **URL** | Valid URL links | | **Custom** | Text that matches the defined pattern | ### Insert a checkbox column Checkbox columns or measures are used to capture binary inputs such as selection, approval, or status. To insert a checkbox column: 1. Go to **Planning** > **Insert Column** > **Checkbox**
2. Enter a title and configure the required settings in the side panel that opens when you select **Checkbox**, as shown below.
3. Select **Create**. After creating the column, select a checkbox to check or uncheck it. You can[ modify a checkbox column](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/insert-manual-input-columns#modify-column-properties) to edit its configuration.
You can configure checkbox column properties similar to other data input columns. For more information, see [configure data input column properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns#configure-data-input-column-properties) and [configure dropdown properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/dropdown#configure-dropdown-properties). Checkbox columns are commonly used for filtering and selection scenarios. For example, you can filter data based on **Checked** or **Unchecked** values. The Writeback application within Plan can use these filters to write back only the selected (checked or unchecked) records based on the configured criteria. ### Insert a date column Date columns allow you to capture or populate date values in the report. To insert a date column: 1. Go to **Planning** > **Insert Column** > **Date.**
2. A side panel opens, as shown below, where you can enter a title and configure the required properties.
3. Select **Create,** an empty date column is inserted into the report with default configuration. 4. To enter a date, double-click a cell and select a value from the date picker or calendar.
You can [modify a date input column](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/insert-manual-input-columns#modify-column-properties) to update its properties. ### Configure date column properties You can define properties such as **Insert As**, **Input type**, **Allow entry on Totals/Subtotals**, **Allow Input**, and **Description**, similar to other data input columns. For more information, see [configure data input column properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns#configure-data-input-column-properties) and [configure dropdown properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/dropdown#configure-dropdown-properties). Additional configurations for date columns include: * **Format**: Select the required date format from the **Format** dropdown.
* **Minimum and maximum date**: Set the allowed date range by defining minimum and/or maximum values. Users cannot enter dates outside this range.
* **Default value**: Pre-fill the column with a default date to avoid manual entry. You can set the default value using: * **Static**: Use the date picker to define a common date for all rows.
* **Measure/Column**: Select a measure or column (native, formula, or date input) to source the default value.
After configuring the properties, select **Create** to insert the column. You can overwrite the default value by double-clicking a cell and selecting a new date from the date picker.
{% hint style="info" %} The default value option is available for both visual measures and visual columns. Invalid date formats are automatically handled as blank values to ensure clean export and writeback. {% endhint %} # Person In Plan, use the **Person** input column to assign users to specific rows in the Planning sheet. ### Create a Person input column 1. Go to **Planning** > **Insert Column** > **Person**.
2. A side panel opens, as shown below, where you can enter a title and configure the required properties. For more information, see [configure data input column properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns#configure-data-input-column-properties) and [configure dropdown properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/dropdown#configure-dropdown-properties).
3. Select **Create**. The Person column is added to the report.
### Add users to the column 1. Double-click a cell in the Person column. 2. Select users from the list by typing a name to search or by scrolling to find the required user.
3. Select one or more users as needed. 4. Press **Enter** to save. The selected usernames are displayed in the column.
# Importing data Inforiver Matrix provides the flexibility to input values in Excel and seamlessly import them into the visual. Using the import utility is as simple as selecting the Data Input measure to input values for -> downloading the Excel template -> entering values directly in Excel, and importing the template back into the Inforiver Matrix visual.

Data import utility

Let's look at the steps involved and the options Inforiver Matrix provides to import data from Excel. **STEP 1:** In the Data import utility pop-up window, use the **Choose Measure** dropdown to select the data input measure for which we are importing data. We can import data for one measure at a time.

Select the measure

**STEP 2:** If you've already entered values in the data input field, you can choose the 'Template with existing values' option to capture the existing values in the template file. Choose 'Blank template' if you do not need the existing values in the template file. Click Download.

Selecting the template type

**STEP 3:** Enter values in the downloaded file. In this case, the values already entered in the report have been captured in the template file. Let's add values for the Central and East regions.

Entering data in the template file

**STEP 4:** Click the **Next** button after entering data in the downloaded template file.

Click Next

**STEP 5:** Click the **Browse** button. Select the template file that contains the new data input values to be imported into the visual.

Select the template file to import values from

**STEP 6:** Select the **Ignore null or blank values in the CSV** checkbox if you do not want to import null values from the template file. Click Apply.

Ignore null values in template file

You'll see that the values we entered for the Central and East regions have been imported into the visual.

Imported data from template file

# Insert Formula columns In Plan, you can insert calculated columns or measures directly into your planning sheets. These can be formatted, rearranged, and reused in subsequent calculations. These measures and columns are created directly on the visual, without modifying the underlying data model. The Excel-like formula engine supports over 50 functions, including logical, boolean, and mathematical functions. The formula editor provides capabilities such as syntax assistance, examples, autocomplete, and multiline editing to help you create and troubleshoot formulas efficiently. In this article, you learn how to insert formula measures and columns and use the formula editor to build the required calculations. {% hint style="info" %} For more information, see [Formula syntax](/documentation/readme/planning-sheets/formula-syntax) for a detailed list of supported functions, operators, and identifiers. {% endhint %} ### Insert a formula measure In this example, consider sales data for two years (2024 and 2025) by quarter. You'll create a measure to calculate percentage variance using the formula: *(2025 Actuals - 2024 Actuals) / 2024 Actuals.* To insert a formula measure: 1. Go to **Planning** > **Insert Column** and select **Formula**.
2. In the **Formula Measure** side panel: * Enter a **Title**. * In **Insert as**, select **Visual Measure** (default). You can also choose **Visual Column** to add a column at the end of the table, outside of the column hierarchy. 3. Enter the formula in the editor and select **Create**.
4. To convert the values of the *Variance%* measure to a percentage format, select the measure and then select the **%** icon on the **Planning** tab.
When the *Variance%* measure is selected, the formula is displayed in the formula bar. ### Working with the formula editor * When you place the cursor in the editor, a context assistant appears with **Functions** and **References**. * As you type, suggestions are filtered automatically. * To reference measures or visual columns, use the **References** tab. * To insert functions such as SUM, MIN, MAX, and AVERAGE, use the **Functions** tab.
* You can also select columns directly from the visual after placing the cursor in the formula editor to insert references.
After creating the measure, you can format it (for example, as a percentage or currency) from the **Planning** tab. ### Configure formula measure properties Configure the properties for formula measures in the same way as other data input measures. For more information, see [configure data input column properties](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns#configure-data-input-column-properties). To edit the properties or to hide or delete a formula column, use the [Manage measures](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/insert-manual-input-columns#modify-column-properties) menu. {% hint style="info" %} #### Note If the report does not contain a column hierarchy, the **Insert as** **Measure** option behaves like inserting a visual column by default, even if **Visual Measure** is selected. {% endhint %} ### Handle calculation errors You may encounter errors such as division by zero. You can handle these in the following ways: #### Use appearance settings * Go to **Format** > **Appearance** > **Numbers**. * Enable **Suppress calculation errors**. * Provide a custom value (for example, 0 or N.A.) #### Use functions in formulas * Use **IFNA**, **IF**, or nested IF statements to handle conditions explicitly and replace error values. ### Aggregation for formula measures By default, **Row aggregation** is set to *Formula* and **Column aggregation** is set to *Sum.* You can modify aggregation settings from the **Manage aggregation** interface. For more information, see [manage aggregation](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/manage-aggregation).
{% hint style="info" %} #### Note If you select **Weighted average** as the row aggregation, the column aggregation is also set to weighted average and cannot be changed. {% endhint %} # Importing data Plan provides the flexibility to import values from Excel into the report using the Data Import feature. Go to Settings > Import. {% hint style="info" %} The import icon is enabled only if a Data input measure/Column is inserted into the report. {% endhint %} Using the import utility is as simple as selecting the Data Input measure to input values for -> downloading the Excel template -> entering values directly in Excel, and importing the template back into the Plan. Let's consider an example of importing values for the Status Data input Column into our report. **STEP 1:** In the Data import utility pop-up window, use the **Choose Measure** dropdown to select the data input measure for which we are importing data. We can import data for one measure at a time. Here we have selected it as Status.

Data Import Utility model.

**STEP 2:** If you've already entered values in the data input field, you can choose the 'Template with existing values' option to capture the existing values in the template file. Choose 'Blank template' if you do not need the existing values in the template file. Click Download.

Selecting the Import Template option.

**STEP 3:** Enter values in the downloaded file. In this case, the values already entered in the report have been captured in the template file. Let's add values for the Artisan Ale and Cosmic Craft Brews brands. Data Import Utility feature updates new values while retaining the original values where no updates are needed.

Entering Data in the Template File

**STEP 4:** Click the **Next** button after entering data in the downloaded template file.

Click Next

**STEP 5:** Click the **Browse** button. Select the template file that contains the new data input values to be imported into the visual.

Select the template file to import values from.

**STEP 6:** Select the **Ignore null or blank values in the CSV** checkbox if you do not want to import null values from the template file. Click Apply.

Ignore null values in the template file

You'll see that the values we entered for the Artisan Ale and Cosmic Craft Brews brands have been imported into the visual.

Imported data from template file

# Invert sign In the case of financial reports such as the P\&L statement, data for expense or deduction items are often shown as positive numbers. This is especially true when the income and expense values come from different tables. However, the report might need to be updated with the negative sign operator for two main purposes. (a) for performing aggregations, say for naturally aggregating revenue & expense accounts to calculate profit, or (b) to display +/- signs in the table to indicate which accounts are added vs. deducted. This can be easily achieved using the 'Invert' option in Plan. ### 1. Cell level You can invert cells or rows and display the negative signs at a cell level. You can also define whether the negative signs should influence the total or not. Let’s invert the 'Beverages' row in the 'East' subregion. a) Select the required row. In the ‘Insert’ tab of the toolbar, click the ‘Invert’ option.

Invert sign

b) The row 'East -> Beverages' is inverted and you can see the change in the value of totals.

Result

c) If you do not want the sign inversion to impact the totals, turn off the 'Include in Total' toggle in the Display -> Numbers settings.

Include in total

d) Click ‘Invert’ again with the respective row selected to reset to the default value.

Reset inversion

e) A pop-up opens with a message as shown, select ‘Apply’ to reset.

Pop-up message

f) You can now see the original values in the Beverages row and the totals.

Reset to default

g) Instead of inverting the sign of the child nodes individually, you can do it in bulk at the parent level. Let's invert the 'United States' parent row, select the row and apply 'Invert'.

Invert sign at parent level

h) You can see that parent and the corresponding child rows are inverted as shown.

Result

i) Note that the signage of calculated rows cannot be changed. In the below image, you can see the 'Invert' option is disabled for the calculated row 'Sum'.

Calculated row

### 2. Row header level Sign inversion can be shown at the row header level with the ‘Sign in headers’ option. a) In the ‘Home’ tab of the toolbar, click ‘Display’ -> 'Numbers'.

Number settings

b) Turn on ‘Sign in headers’ to display the signage in the row headers as shown.

Sign in headers

c) You can also show positive signs and equals on other data source rows and total rows by enabling the 'Show positive sign' and 'Show equals on totals' options respectively.

Show positive & equals

### 3. Measure level a) Create a new signed measure This feature is a single-click solution to creating signed measures. To invert a particular measure, select it and click on the 'Invert' button in the Insert ribbon.

Invert measure

Click on Create to insert the new calculated measure in the report.

Inverted measure created in the report

b) Invert sign for individual measures You can change the sign for a single measure at both child and parent levels. For a given row, select any cell and click on the ‘Invert’ button. Notice how the sign for the Discount measure has been flipped in the example below.

Invert sign

When hierarchical data is involved, you can also change the sign for a particular measure at the parent level, this will be cascaded to all the child rows.

Invert measure for parent rows

### 4. Sign from a source table Instead of defining positive and negative signs at the report level, you can use the sign conventions from your data source tables. To do this, create a column that assigns values -1, +1, and 0 for negative, positive, and totals respectively. A sample table is shown below.

Sign table

a) Add this sign column in the 'Sign' field. You can see the change in the values in the below image and also the calculation of subtotals and totals.

Sign measure

b) On enabling the 'Sign in header' option, you can see the positive, negative, and total rows with the signs in the header.

Sign in headers

c) The 'Invert' option is disabled when the sign column is added.

Invert disabled

{% hint style="info" %} When a sign field is added, you cannot add column fields. {% endhint %} In the next section, we'll be covering the [Audit log](broken://pages/NsVIXWKzmlsDJBYQKpZH) feature. #### Resources [Invert signs for reporting in Microsoft Power BI with Scenarios](https://www.youtube.com/watch?v=mZaQgXQYDhY) # Quick formula Inforiver provides several one-click calculations for inserting columns and rows. ### 1. Quick formula Columns/measures such as running total, % contribution to parent/grand total, etc. can be inserted in a single click. These options can be accessed from the Insert tab > Quick formula.

Quick formulas in Inforiver

When you write custom formulas, you can also reference quick formula measures in them.
#### i) Running total The following configuration options are available for 'Running total'. Insert as - The running total can be inserted as a [measure or column](https://docs.fabricplan.com/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/pages/lJKvzuF7vHqgAxSIHXkl#2.-measure-vs-column). Based on Measure - Column/measure based on which the running total needs to be calculated.

Running total for Actuals

If you want to calculate the running total for all leaf nodes, irrespective of the level of the hierarchy they belong to, select the 'Continuous Total' checkbox.

Continuous running total for hierarchical datasets

#### ii) Percentage running total This formula is a variant of the running total quick formula. The running total is first calculated in the background at each level of the hierarchy, after which it is displayed as a percentage of the total/subtotal.

Percentage running total formula

#### iii) %Growth/Decline This option allows you to insert visual measures or a column that calculates the required percentage growth or decline of the chosen measure.

%Growth/Decline

#### iv) % Contribution to parent Use this option to quickly calculate each member's contribution to their parent's total value. In addition to choosing to insert as a visual measure/column and the base value, a progress bar can be enabled along with a percentage and/or value to display.

% Contribution of 2022 Actuals to parent

#### v) % Contribution to grand total Contribution to the grand total can be inserted as shown below. To insert % contribution only for a selected category instead of all the categories or only for the column grand total, choose the 'Visual column' option and the corresponding column.

% Contribution to grand total as visual column

#### vi) Lead/lag You can perform a lead /lag calculation to shift any trend data by 'n' periods. This helps you quickly perform downstream variance calculations such as QoQ growth, YoY growth, etc. for each period.

Lead/lag calculations

#### vii) Overall ranking Ranks can be added across different groups in a single click. Choose to insert as a measure or column and the base measure. Note that there are no ranks applied for the subtotals.

Overall ranking

#### viii) Ranking within a group Ranks within groups can be added including ranks for subtotals as shown in the below image.

Ranking within group

#### ix) Insert variance The default variance calculated by Inforiver is based on the first measure added to the AC/PY/PL/FC fields. *Insert Variance* can be used to calculate the variance between any native measures, simulations, or formula fields in your report.

Insert variance

#### x) Empty Column This option allows you to insert an empty column into the report to improve readability and visual appeal. Values cannot be entered in this column.

Empty Column

#### xi) Empty Measure This option allows you to insert empty measures into the report to improve readability and visual appeal. Values cannot be entered in these measures.

Empty Measure

### 2. Smart analysis % contribution and variance % columns can be added in a single click based on any cell in the visual. These can be accessed in the Insert tab -> Smart analysis as shown below. {% hint style="info" %} You need to select a cell to enable the 'Smart analysis' option. {% endhint %}

Smart analysis

In the below image, % contribution has been added based on 2022 Actuals -> East.

Smart analysis - Contribution

In the below image, % variance has been added based on the grand total. But you can choose any other value as well. Note that the calculation gets added as a measure or column based on the presence of categories in the column field.

Smart analysis - Variance

### 3. % Contribution rows % Contribution based on any row can be inserted in a single click. To achieve this, in the Insert tab-> Insert row, select '% Contribution row'. {% hint style="info" %} Select a row to enable the 'Insert row' option. {% endhint %}

% Contribution row

The % contribution row gets added as shown below.

% Contribution row added

# Visual column inside visual measures We have covered the different types of manual input columns that can be inserted in the report in the previous sections. We have also learned the steps to [insert calculated columns](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-calculated-columns), in the form of visual measures as well as visual columns. In this page, we will look at a specific case where you can include a data input visual column to be referenced inside a visual measure formula. Data fields like interest rates, discount rates, tax slabs, and foreign exchange rates might be common to all the column dimensions. Hence they would be created as visual columns and we might need to refer to them often in calculations. In such cases, you can reference those visual columns in visual measures using the **COLUMNS** prefix. Let us consider the following example: a visual input column is included to specify a discount rate of 10%.

Visual Column - Discount%

To calculate the new sale price, we can insert a formula measure.

Inserting formula measure

In the formula, we need to refer to this visual column, **Discount*****%*** to calculate the discounted sale price. The visual column is referred to using the **COLUMNS** prefix identifier.

Visual column referred inside a visual measure

Click **Create** and the result is shown below:

Discounted Price

{% hint style="info" %} In the current edition, visual measures can only refer to **data input** visual columns and not calculated visual columns (formula columns). {% endhint %} ### 2.1.3. Insert rows from Look-up Tables You can also insert rows and their leaf categories from Lookup Tables. **STEP 1:** To pick and upload row categories from Lookup tables, navigate to 'Home' > 'Manage Rows' > 'Row Settings' > 'Insert Row Configuration' > 'Manage'. In the Insert Row Configuration pop-up, select **Options list from Lookup Tables** from the Type dropdown.

Options list from Lookup Table.

**STEP 2:** The **Add options from Semantic model** window opens. In the **Table Connection** tab, select the workspace, semantic model and the table you need to connect to. Click **Next.**

Table Connection

**STEP 3:** Go to the **Options configuration** tab and choose the column from the connected table that will be used as the option label. In the example below, we have chosen the column 'Category' from the table 'Contoso-Product'. This column's members will be used to populate the 'Categories' row level.\ \ **STEP 4:** You can optionally specify a filter if hierarchical data is involved, and you want to maintain the same structure in your reports. In the **Columns** dropdown**,** select the field based on which you need to filter the options. In this case, it is the Contoso - Product.SubCategory which can be used to identify the Category. Select the matching field from the visual (SubCategory) in the **Visual Column** dropdown. Then click **Add**. Because of this filter, when you insert a row and add a sub-category first, you will be prompted with the category to which it belongs.

Options configuration

{% hint style="info" %} Configuring filter options is optional. By adding it, we ensure that the dropdown list for each category displays only the relevant sub-categories and vice-versa. For example, if you choose "Cellphones" as the category, only the sub-categories under "Cellphones" will be displayed, not other categories. Similarly, when you choose a sub-category, you can add it only under the relevant category. {% endhint %} **STEP** **5: F**ollow the same steps from 1 to 4 for SubCategory dimension.

Options list from semantic model

Options Configuration

Configuration done

Once the configuration is completed as shown in the image above, you can follow the steps in sections 2.1/2.2 to insert the configured row categories into your visual. You will see the data from the semantic model in the Category and Sub Category dropdowns.

Insert rows from semantic model

The rows are inserted as shown below:

Inserted rows

{% hint style="info" %} The base data in our visual may be from one table, but the options in the dropdown may be from a different table; in such cases, you can specify a join. For more information on using joins and filters, please refer to [this section](https://docs.fabricplan.com/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/pages/Z1YktABi2gOmdrc8a7K2#id-4.-using-joins-and-filters). {% endhint %} # Manage inserted rows Plan allows you to manage rows inserted in a visual, such as calculated rows, static rows, template rows, or data input rows, using the **Manage Rows** option. In this article, you learn how to manage inserted rows using options such as search, filter, show, and hide. ### Manage an inserted row You can manage inserted rows (such as static and calculated rows) using the **Manage Rows** option. A list of inserted rows is displayed, and each row type (calculated, aggregated, data input, and template) is identified using icons. To manage an inserted row, 1. Go to **Planning** > **Insert Row** > **Manage Rows** > **Rows** or **Template Rows.** 2. The **Inserted Rows** side panel opens. Hover over a row to view the available management options.
#### Available actions * **Add rows**: Add child rows or sibling rows (at the same hierarchy level) directly from the **Manage Rows** pane. * **Edit**: Modify formulas or other row properties. * **Show or hide rows**: Temporarily hide inserted rows. * **Delete**: Permanently remove inserted rows. * **Show row indicator toggle**: Show or hide the pencil icon displayed next to inserted rows.
* **Search and filter:** Search for a particular row category or filter rows based on the type.
### Row settings The **Manage Rows** pane includes a **Row Settings** tab that allows you to configure settings for inserted rows.
#### Insert row configuration Use **Insert Row Configuration** to control the types of rows that can be inserted. You can define category-based rules for bulk row insertion. The following options are available: * **Text** – See [*Text*](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-manual-input-rows#insert-a-row-hierarchy) * **Distinct Values** – See [*Distinct Values*](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-manual-input-rows#insert-distinct-values-as-rows) * **Disable Insert Row** – See [*Disable Insert Row*](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-manual-input-rows#disable-row-insertion) * **Options list from CSV** – See [*Options list from CSV*](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-manual-input-rows#upload-rows-from-excel-or-csv) * **Allow Blank Values** - See [*Allow Blank Values*](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/rows/insert-manual-input-rows#allow-blank-values-in-categories) # Manage inserted measures & columns Measures and columns inserted in the visual such as calculations or data input columns can be managed using the **Manage Measures** option. ## 1. Measures and columns Similar to the [Manage Rows](#id-1.-manage-rows) option, the **Manage Measures** option displays a list of inserted columns/measures with options to edit, delete, or hide them. All the measures created in a visual can be viewed in the Measures tab. Visual columns can be viewed and managed in the Columns tab. In addition, there is also an 'Insert New' option which lets you insert calculated columns or data input columns right from this panel.

Managing inserted columns

Use the duplicate icon to quickly replicate formulas that need to be reapplied in similar calculations.

Duplicate formula

## 2. Settings The **Manage Measures** side panel has a **Settings** tab that lets you control and configure important settings concerning the rows and measures, such as user permissions, time interval mapping, row dimension ID mapping, etc.

Settings pane

### 2.1. Data input access Plan allows you to set explicit read/write access for specific users on data input and forecast columns. To set it, click **Home -> Manage Measures -> Settings** **->Data Input Access ->** **Manage.** You can add the required users in the pop-up window. You can Add Users to give access by clicking on the Pencil icon that appears when you hover. You can also reset using the reset option.

Data Input Access

There is an Enable Visual Adjustment Toggle, which is enabled only after giving Read/Write access to a user. When enabled, this toggle allows the user with read-only access to request a change in data. To know more, [click here](https://docs.fabricplan.com/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/pages/lJKvzuF7vHqgAxSIHXkl#id-3.1-enable-visual-adjustments). {% hint style="info" %} Access control for all the columns can also be set through the **Manage Columns** dropdown as explained [here](https://docs.fabricplan.com/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/pages/lJKvzuF7vHqgAxSIHXkl#id-3.-access-control). Specifically for the forecast columns, the user access can be configured [here](broken://pages/WOvNP6Oj58LBDUNHmuTy). {% endhint %} ### 2.2. Time interval mapping When you have a date hierarchy in your rows/columns, you can use the time interval mapping to verify the formats that Plan auto-detects. Hover over theicon to view all the supported date formats.

Supported date formats

You can also choose whether to use Power BI sorting or Plan sorting. When the **Sort** toggle is enabled, any sorting changes made at Power BI level will not be reflected in the visual. In the example, notice how the Power BI sorting is not applied on the Quarter when the Sort toggle is enabled.

Sort precedence

### 2.3. Dimension ID mapping Plan automatically assigns a dimension ID to each row dimension to uniquely identify them. This mapping helps to track and manage them, especially during operations like writeback. Visual components such as formatting, notes, comments, and data inputs added to the rows are also linked to the dimension IDs. When row dimensions are renamed/changed, we might lose these visual elements added. To avoid this, Plan allows us to map the row dimensions to a constant dimension ID. In the example below, we’ve applied formatting, added notes, and comments, and added data inputs to the products, *Juices* and *Tea & Coffee*.

Sample report with formatting, comments and data input columns

The visual elements were lost after the product names were renamed to *Fruit Juices* and *Chai & Coffee* respectively.

When the row dimension 'Product Name' is renamed

To avoid this, let us map the dimension IDs to a constant dimension, such as the *Product ID* instead of the *Product Name*. To do so, navigate to **Insert -> Manage Measures -> Settings -> Row ID mapping -> Manage**.

Manage Row ID Mapping

The image below shows the default row dimension ID mapping.

Default row ID mapping

Let us map the 'Product Name' to the constant dimension, 'Product ID' as shown below. Click **Apply**.

row dimesion ID mapped manually

Note that changing the mapping removes all the initial formatting and visual elements associated with it. Any formatting, notes, comments, and data inputs added after mapping with the constant dimension ID are retained as shown below, including during the writeback.

After manually mapping row ID dimensions

**Updates due to skipped row ID dimensions reflected in writeback data** After mapping the row IDs to a constant dimension, not only the visual formatting, data changes, notes, comments, etc., are retained in the report but also captured in the writeback data promptly. The report below shows the default mapping, followed by the results of the writeback data.

Writeback data before the change

After manually mapping the row dimension to a constant ID like the Product ID, any changes made in the report are also effectively captured in the writeback. In the image below, you can see the updated records in the report that are also reflected in the writeback data.

Writeback data after row ID mapping and data changes

### 2.4. Schedule backup for measure You can retain point-in-time measure values with the new scheduled backup feature. The measure values are captured as separate read-only visual columns within the same visual. With this feature, you can easily calculate historical variances and analyze fluctuations in your numbers. Navigate to Manage Measures > Settings > Schedule Backups for Measure to create monthly, weekly, or daily schedules.

Creating a measure backup schedule

* **Start and End date:** Specify the time frame during which the measure should be backed up. * **Schedule Time:** The time and timezone at which the backup job should be triggered. * **Frequency**: Opt for daily/weekly/monthly schedules. * For daily backups, you can specify the number of backups to create. For weekly schedules, you can select the day of the week to take a backup. For monthly schedules, you can specify which day of the month to take a backup on and additionally opt to take a backup on the last day of the month as well.

Daily schedule

Weekly schedule

Monthly schedule

* **Measures**: Select the measures that need to be backed up. * **Backup name:** Enter the name of the backup field. You can run ad-hoc backups by clicking the **Run now** icon.

Ad hoc backups

{% hint style="info" %} Measure backups will work only after the report is saved in Power BI. The report should also be created in a common workspace. {% endhint %} The image below shows a report with backed-up measures:

Measure backups

Measure backup jobs have dedicated logs that show the status of the backup, milestones, and the backups that are deleted when the limit is reached. Click the **View jobs** option from the Schedule Measure Backup side pane.

View jobs

Scheduled measure backup logs:

Scheduled measure bkp logs

### 2.5. Show all periods from Infobridge Infobridge supports forecasts and allows you to capture projections or data for future time periods. Your target reports, however, may not require future data. The **Show All Periods from Infobridge** option in the Manage Measures side pane can be used to control whether time periods that do not exist in the target visual should be imported from Infobridge: * When the toggle is enabled, all the time periods available in the bridge will be imported into the target report, irrespective of whether they exist in the target visual. * When disabled, only the time periods in the bridge that exist in the target visual will be imported. This option will be enabled only after creating an integration to a bridge

Show All Periods from Infobridge option

In the bridge, we have the allocated budgets for 2023 and 2024 as well as the projected budgets for 2025.

Projected and allocated budgets in the bridge

The target report contains the profits for 2023 and 2024. We need to integrate the budgets for 2023 and 2024 with this report. The projected budgets for 2025 are not required in this case.

Profits in the target visual

Since we've disabled the **Show All Periods from Infobridge** toggle**,** when we insert the budget from the bridge, it will be inserted only for the time periods that exist in the target report.

Inserting budgets for existing time periods

# Manage aggregation Custom Measure Aggregations in Semantic Models with Plan When working with **semantic data models**, there are scenarios where you may need to apply **custom aggregations** for specific reports. For example, instead of using the default aggregation defined in the data model, such as the **sum of quantity sold,** you might want to calculate the **average quantity sold** for more accurate analysis. **Plan’s aggregation feature** enables you to override native measure aggregations without requiring extensive changes at the data model level. This flexibility helps analysts and finance teams tailor calculations to specific reporting needs while preserving the integrity of the underlying model. With Plan, you can: * Define custom aggregations at the **measure or column level** * Apply aggregation rules at the **hierarchy level** * Control how aggregations behave for **row subtotals, column subtotals, and grand totals** This powerful aggregation control ensures consistent, accurate reporting across different business views without duplicating measures or modifying core semantic models. **Row aggregation:** You can specify the aggregation to be applied to row subtotals and grand total.

Row aggregation

**Column aggregation:** You can specify the aggregation to be applied on the column grand total and subtotals.

Column aggregation

We will see in detail about how to set these aggregations below. Click on the **Aggregation** button in the Settings tab to open the **Manage Aggregation** interface.

Manage Aggregation panel

The Manage Aggregation panel offers you three tabs: * Measure: Lets you apply the aggregation at the Measure level * Hierarchy: Lets you apply the aggregation at the Hierarchy * Row Aggregation: Lets you apply aggregation at the Row Level ### 1. Measure level aggregation You can specify the row and column aggregation for individual measures and columns from the **Measure** tab. Select the aggregation type from the dropdown against the measure name.

Measure level row and column aggregation

* **Sum** set as row aggregation for 2026 Revenue: The sum of the child rows will be used to populate the row subtotal and grand total. * **Maximum** set as row aggregation for 2026 Gross Margin: The maximum value in the child rows will be used to populate the row subtotal and grand total. * **Minimum** set as column aggregation for 2024 Revenue and Gross Margin: The minimum values in the columns will be used as the column grand total and subtotal. ### 2. Row Level Aggregation Row-level aggregation allows you to set the aggregation for each row separately. In the highlighted example below, the 'Hard Seltzer' category is the sum of its immediate child nodes and has 'Sum' Aggregation, while the 'All' row has an Aggregation type set as 'Minimum'.

Row Level Aggregation

### 3. Hierarchy Level Aggregation Hierarchy Level Aggregation allows you to set the aggregation type at the Hierarchical level Consider the example below, where the category hierarchy aggregation is set as 'Sum'. Each category has the sum of its child nodes.

Hierarchy Level aggregation

### 4. Aggregation types Plan offers various types of aggregation types built in to suit various business needs. Here we will discuss the aggregation types in detail. #### Native By default, the 'Native' option is applied which follows the native summarization set in Power BI.

Native aggregation at report level

#### None Choosing 'None' performs no aggregations for the selected measure or hierarchy. In the example below, the 'Units Sold' measure is not aggregated.

Aggregation is 'None'

#### Sum 'Sum' aggregation displays the sum of the immediate child nodes as the aggregate. In the highlighted example, the 'Nin Alcoholic Beverages' category is the sum of its immediate child nodes.

Aggregation is 'Sum'

#### Minimum 'Minimum' aggregation displays the minimum value of the immediate child nodes as the aggregate. In the example below, the 'Gross Margin' displays the minimum value among its child nodes which is the value of 'Beer'.

Minimum aggregation type

#### Maximum 'Maximum' aggregation displays the maximum value of the immediate child nodes as the aggregate. In the image below , the 'Gross Margin' displays the maximum value of its immediate child nodes which is the value of 'Beer'.

Maximum aggregation type at report level

#### Average (Children) 'Average (Children)' aggregation displays the average value of the immediate child nodes as the row aggregation. In the example below, the 'Gross Margin' measure is aggregated as 'Average (Children)'. The Average of the Categories are calculated under the 'Average(Children)' aggregation.

Average(children) aggregation type

{% hint style="info" %} When Average excluding zero is selected, the zero values are excluded from being considered for the average calculation. {% endhint %} #### Average (Leaf) 'Average (Leaf)' aggregation displays the average value of all the leaf nodes of the row category as the row aggregation. In the below example, 'Gross Margin' is calculated as the average of the leaf nodes of all the variant values.

Average(leaf) aggregation type

#### Standard deviation 'Standard deviation' aggregation displays the standard deviation of the child rows as the aggregate. In the example below, 'Gross Margin' is aggregated as the standard deviation of its child nodes.

Standard Deviation as aggregation type

#### Visible rounding 'Visible rounding' aggregation rounds off values in a way that the individual values add up properly to the subtotal and grand total. This is a very common requirement in external financial statement reporting, such as the income statement and balance sheet reporting.

Visible Rounding as an aggregation type at report level

#### Weighted Average In weighted average aggregation, each child value in a row category is multiplied with weights taken from another measure which are then summed and divided by the total weight. Weighted moving average = (w1\*a1 + w2\*a2 +...+wn\*an) / (w1+w2+...+wn), where n= number of child rows in the row category w1,w2,w3,....wn = weights (data from measure 1) a1,a2,a3,........an = data from measure 2 This type of averaging is sometimes more accurate than simple averaging as it considers the varying importance of the data points. This also smoothens any price point fluctuations and is commonly used for inventory accounting, portfolio analysis, statistical research, planning, and forecasting. The example below calculates the weighted average using the costs and quantities in each region. i.e., Average Cost for Data Input Measure = \[(Quantity\*Cost) *in Ready to Drink Cocktail*+ (Quantity\*Cost) *in* *Non-Alcoholic Beverages*+ (Quantity\*Cost) *in Hard Seltzer* + (Quantity\*Cost) *in Beer* ] / Total Quantity. {% hint style="info" %} Note that weighted average is a row aggregation method – only the total and subtotal rows will reflect the calculation. {% endhint %}

Weighted Average as the row aggregation type for data input measure

{% hint style="info" %} 'Weighted Average' aggregation can be applied only to data input, formula, and forecast measures. {% endhint %} #### Formula When 'Formula' is selected, then the Formula used for computing the values of the Formula column is used for computing the Totals and Subtotals.

The formula is chosen in the Aggregation type.

{% hint style="info" %} Applicable only for Formula Columns and Measures. {% endhint %} #### First This aggregation type is often used in time-series data or any sequential dataset. The 'First' aggregation type displays the first value from the set of immediate child nodes. This is especially useful when we need to record the dataset's initial state or value. In the example below, the values from the first period are used for the column subtotals and grand totals. In the example, the Revenue from Q1 2026 is used as the subtotals at the year level. The Revenue from 2024 (the earliest year) is used for the grand total.

First aggregation

#### Last This aggregation type is used in time-series data or any sequential dataset. The 'Last' aggregation type displays the last value or the latest value from the set of immediate child nodes. This is useful when we need the most recent value in a dataset, especially when budgeting for the following year by carrying over the previous year's value. In the example below, the last period is used to populate the grand totals and subtotals. The Revenue of Q4-2026 is used to populate the yearly subtotal. The Revenue of 2026 is used to populate the grand total.

Last aggregation

Refer to know in detail about [Row Aggregation](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/manage-aggregation/row-aggregation). Refer to know in detail about [Column Aggregation](/documentation/readme/planning-sheets/how-tos/7.-planning-budgeting-and-forecasting/column-aggregation). # Row aggregation If your data is hierarchical, **Plan** provides flexible options to apply **row aggregations** across all branches at a selected hierarchy level. This allows you to define **different aggregation methods for multiple hierarchy levels at the same time**, ensuring accurate and consistent data rollups. To apply a **uniform aggregation method** to a specific hierarchy level, go to **Settings > Aggregation >** the **Hierarchy** tab in the **Manage Aggregation** dialog box. From there, you can easily assign and manage **different aggregation methods for each hierarchy level**, giving you full control over how hierarchical data is calculated and analyzed. Row aggregation only applies to sub-total and grand-total rows. {% hint style="warning" %} The aggregation type set in the Hierarchy tab will not be applied to formula measures or quick measures created in the Planning sheet. {% endhint %}

Aggregation set for hierarchy levels

The specified aggregation method is set for a specific level of the hierarchy.

Hierarchy-level aggregation

### Other methods to set row aggregation #### 1. Setting aggregation from manage columns Another way to set row aggregation for measures is by clicking on **Manage Columns** and then the ![](/files/lj5foH8KX1Xbab61GqtL) icon. This allows you to manage row aggregations for specific measures.

Settings in 'Manage Columns' dropdown

A pop-up displays a list of measures under the **Display** section. You can change both the Row and Column aggregation types of multiple measures here.

Manage columns pop-up

Let's change the Row aggregation for 'Revenue' to 'Minimum' and 'Gross Margin' to 'Maximum'.

Row aggregation

{% hint style="info" %} You cannot set aggregation for a simulation measure. {% endhint %} Note that the [row aggregation type](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/measures-and-columns/insert-manual-input-columns/insert-manual-input-columns#i-row-aggregation-type) for calculated columns and manual data input columns can also be defined here in the **Manage Columns** dialog box in addition to the **Insert formula** and **Data input** [side panels](https://docs.fabricplan.com/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/manage-aggregation/pages/d1U162wTDoFssKWgWuRI#id-3.-properties). #### 2. Setting aggregation from column gripper To set aggregation for a specific measure, you can also click on that column's gripper and choose 'Aggregation' from the context menu as shown below. Click on the required aggregation type to apply it to that measure. In the example below, the 'Gross Margin' measure has been aggregated to display the maximum value of the child rows using the aggregation method 'Maximum'.

Set aggregation from the column gripper

To learn how to manage column aggregations, click [here](/documentation/readme/planning-sheets/how-tos/7.-planning-budgeting-and-forecasting/column-aggregation). # Pivot data Business reporting and analysis often involve pivoting data and interchanging the order of row dimensions. Under the 'Pivot' option, Plan allows you to **reorder, show, or hide specific row dimensions.** This option allows you to create aggregated tables or views within the planning sheet, with different combinations of row dimensions. There is also a Pivot Explorer, which, when enabled, retains all your pivot views so you can easily switch between them for analysis. ## 1. Create a Pivot View Creating pivot views inside your planning sheets is a simple, straightforward process outlined below: **STEP 1:** Click **Plan > Pivot.**

Click on 'Pivot'

**STEP 2:** Enter a relevant name for the pivot view.

Pivot view name

**STEP 3:** Add required row dimensions by selecting the checkboxes. Click **Save**.

Add row dimensions to pivot view

The required pivot view is created, as shown below, with all data aggregated by region.

Pivot view—region-wise

## 2. Add more Pivot Views **STEP 1:** Click on the **Pivot** option again and click the **Add** link to create more pivot views.

Add new pivot view

**STEP 2:** Name this view, add the required row dimensions, and click **Save** to add the new pivot view.

New pivot view

## 3. Pivot Explorer We have created two pivot views, and you can switch between them using the Pivot Explorer.

Pivot Explorer

* Use the pencil icon in the pivot explorer to configure new views or edit existing views. * You can close/disable this explorer by clicking on the 'x'. * To enable Pivot Explorer again, click the down arrow below the 'Pivot' option and toggle the button.

Enable/Disable option for Pivot Explorer

## 4. Duplicate or Delete a Pivot View To duplicate or delete a pivot view, click on the highlighted button beside the view and then choose the required option, as below:

Duplicate or delete a view

Even within each pivot view, you can create data inputs, forecasts, formulas, snapshots, and scenarios corresponding to each combination of dimensions available. The pivot view/table you create also lets you enter numbers at the higher level and allocate them proportionally to all the relevant sub-levels. Notice how the single select (Status) and formula (GrossProfit) values appropriately change based on the pivot selection.

Switching between pivot views

{% hint style="info" %} [Infobridge](/documentation/readme/infobridge) offers the ability to [pivot data](/documentation/readme/infobridge/9.-data-transformations/pivot-table) by interchanging or reassigning fields in rows and measures. You need to [import your planning sheets to a bridge](/documentation/readme/infobridge/how-tos/2.-add-source-to-bridge) for generating different pivot views. {% endhint %} # Conditional formatting Plan enables intuitive data classification using conditional formatting, allowing users to visually interpret performance at a glance Conditional formatting in dashboards and reports is key to guiding users' attention to specific performance areas. Compared to charts, tabular reports have higher information density, which makes conditional formatting a lot more useful. Conditional formatting can be used to highlight or emphasize certain records using color, icons, data bars, etc. Select on the **Conditional formatting** dropdown as shown below.

Conditional formatting-overview

There are two ways to apply conditional formatting - 1. Using quick, one-click options such as quick rules, color scales, classification, and data bars, which are covered in [one-click options](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/one-click-options) 2. Creating advanced rules from scratch using nested if-conditions, ranking, etc., which are covered in [create rule](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings) Inserted rules can be edited, hidden, reordered, or deleted using [manage rules](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/manage-rules). {% hint style="info" %} One-click formatting options can be customized further. {% endhint %} # One-click options With Plan, you can perform various conditional formatting actions in a single click. Options include semantic formatting, color scales, segmentation, icons, ratings, data bars and many more. A big benefit of the one-click setting is that you do not need to create rules from scratch. After you create a quick rule, you will have the option to customize it in a detailed manner if your report requires it. ### 1. Quick rule Quick rule allows you to highlight the positive and negative values in the visual in a single click. Under 'Quick rule', there are two options - 'Positive' and 'Negative'.

Quick rule

{% hint style="info" %} The one-click conditional formatting options are enabled only if a cell/column is selected. {% endhint %} a) Select a column. You can highlight values greater than or equal to zero in green by clicking on the 'Positive' option.

Highlight positive values

b) Similarly, you can highlight values lesser than or equal to zero in red by clicking on the 'Negative' option.

Highlight negative values

### 2. Color scales A wide variety of color scales such as sequential, qualitative, diverging, continuous and continuous-diverging can be applied in a single click. a) Choose any column or cell and click on Conditional Formatting -> Color scales. Choose any of the color scales shown.

Color scales

b) A sample heatmap using the sequential color scale is shown below. Notice that the font colors are automatically adjusted to be in contrast with their backgrounds for enhanced readability.

Sequential color scale

To learn more about customizing color scales, visit [this section](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings/color-scale). ### 3. Classification You can apply conditional formatting based on icons/ratings and even deliver ABC segmentations in a single click. As you click on 'Classification', you can see the following options.

Classification

Let's look at an example for the three categories - Text, Icon set, and Rating. **3.1.** **Text**: Text-based classification is commonly used to categorize performance. A sample report with ABC classification is shown in the below image.

ABC classification

**3.2.** **Icons**: You can deliver conditional formatting using icons in a few clicks. Here is an example where items that contribute the most to sales are highlighted.

Icon-based conditional formatting

**3.3.** **Ratings**: You can use ratings such as the star-based ratings commonly seen in e-commerce sites in the product feedback section.

Star-based ratings

To learn more about customizing classifications, visit [this section](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings/classification). ### 4. Data bars Data bars can be used to insert colored bars inside a cell to show how a given cell value compares to others. a) On clicking 'Data bars', you can see the following options. Select any of the options.

Data bars

b) Data bars get added as shown in the image making it easy to spot the highest and lowest values at a glance.

Data bars

To learn more about customizing data bars, visit [this section](https://docs.fabricplan.com/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/pages/sZZ2BIb2LTqZXiMV7252#2.-data-bars). ### 5. Action Analysis #### a) Action dot Action dots represent the degree to which the cell values deviate from the desired range/value in the measure. Adding them to the cells helps you assess how the quantities compare to the middle value or any desired value in the measure. 1. Select any measure to which you want to add them. Click **Conditional Formatting -> Action Analysis -> Action Dot.**

Action Dot

2. By default, Planning sheet provides custom color ranges based on values or percentages. Click **Apply.** Find below a sample report with action dots applied to the *Profit* measure.

Action dots applied to Profit

The color scale indicates a positive or negative deviation, and the number of dots represents the magnitude of the difference, with which you can focus on the values that diverge the most. #### b) Action Color Action colors are a variation of action dots in which the degree of divergence of cell values from the target value is expressed by color gradients. The color scale denotes a positive or negative deviation, while the gradient intensity indicates the magnitude of the deviation. 1. Select any measure to which you want to add them. Click **Conditional Formatting -> Action Analysis -> Action Color.**

Action Color

2. By default, Planning sheet provides custom color ranges based on values or percentages. Click **Apply.** Find below a sample report with action colors applied to the *Profit* measure.

Action Colors applied to the Profit

The conditional formatting side panel provides options to customize the action dots and action colors. The options are similar to the color scale, data bars, and classification which are explained [here](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings/color-scale#c-custom-color-scale) and [here](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings/classification#classification-ranges). The **middle percentage** option is used to set the desired median percentage value from which the spread is calculated. ### 6. Bubble charts You can use bubble charts to gauge the magnitude of a measure instantly and for quick comparative analysis.

Bubble chart conditional formatting

The bubble size can be determined by comparing values in the same column or within the same row. You can also use a different measure to determine the size of the bubble.

Bubble charts

You can use the side panel to customize your rules. In the next section, we'll look at the [advanced options](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings). # Create rule You can build your own conditional formatting rules from scratch or customize the rules created using the one-click options if your report requires it. In this section, we'll cover some basic settings required to configure conditional formatting. **STEP 1:** To create a new rule, select Conditional Formatting -> Create Rule from the Home ribbon. The conditional formatting side pane opens as shown.

Create rule

**STEP 2:** The rule can be renamed by editing the **Title** field. **STEP 3:** You can apply conditional formatting to the row headers, rows, column headers, or columns/measures. Choose the desired option in the **Apply to** field. For example, if you opt to apply conditional formatting for row headers, you will be able to select the row dimensions that will be impacted by conditional formatting.

Conditional formatting for row headers

**STEP 4:** You can apply conditional formatting to values and totals, totals only, or values only. By default, Values only will be selected. Customize as required using the **Row hierarchy levels** field.

Apply to - Options

Row hierarchy levels - Options

**STEP 5:** There are several conditional formats you can use to create rules. You can select the desired format using the **Format by** field. The available options are Rules (If Conditions), Color Scale, Classification, and Ranking.

Format by - Options

In the next section, we'll be covering [Rules (If conditions)](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings/rules-if-conditions). # Rules (If conditions) You can apply conditional formatting using font color, style, icons, or background based on one or more IF conditions. Refer to the [Create Rule](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings) section to get started. Once the rule is created and you can see the Conditional Formatting side panel, follow the below steps. **STEP 1:** Choose 'Rules (If conditions)' in the **Format by** dropdown. You can see two sections - Style and Conditions.

Choosing Rules (If conditions)

**STEP 2:** Using the **Style** section, you can format text based on color/style, apply a background color, or select icons. **STEP 3:** In the 'Conditions' section, you can define conditions using options such as Number, Data selection, Values, Formula, and User Selection.

Format by rules

**STEP 4:** When you have inline charts in your reports, conditional formatting rules can be applied to the chart labels or the chart itself. Select the desired option(s) from the **Impact on** list**.** Notice how the bars for subcategories with sales > 20k are highlighted in blue.

Impact on

Let's look at examples for each of these options. ## Style * **Font style: bold, italic, underline** Set the font style based on conditional formatting rules. Notice how we've applied Bold, Italics, and Underlined the values that satisfy the condition. Click on the icon to apply a color for the underline.

Font style

* **Cell background** Highlight the cells that match a conditional formatting rule by applying a background color.

Cell background

* **Font color** Apply a custom font color when a conditional formatting rule is met.

Font color

* **Cell borders** Highlight cells that satisfy the conditional formatting rule by setting a custom border. You can choose the border color by clicking the icon.

Applying borders

* **Adding icons or text** Display icons to apply conditional formatting on your data. You can also use custom icons by clicking the Upload Icon link.

Applying icons

You can position the text/icons with respect to the cell values. You can also choose to display only the icons or text and hide the cell values.

Using icons and text

When you use icons, you have an additional option to align your icons in the grid. By default, the icons are left aligned.

Aligning icons

Left-align option disabled

* **Hiding values** Business reporting may require withholding certain information to protect strategic interests. You can use conditional formatting rules to mask values based on specific criteria.

Hiding values

* **Hatched fill pattern** Highlight your data with hatched cell backgrounds – this feature allows you to spotlight your data with hatched styling.

Hatched CF

## Conditions ### 1. Number In this example, we are highlighting the subcategories where 2021 Actuals are greater than 10 million. Note that we have selected 'Bold' and selected 'Green' as the font color. The numbers field supports scaled entries such as 10m. Click 'Apply' once the changes are done.

Conditional formatting based on a numeric input

### 2. Data selection You can also use a cell value in the condition. 2.1. Select 'Data selection', click on the 'Set value' field and select a cell in the report.

Applying background color to values and totals

Note: * In this example, we have used 'Values and Totals' as the 'Row hierarchy levels'. You can see a new field where you can choose whether conditional formatting needs to be applied to the row grand total or not. * We are also using a background color for the formatting. 2.2. Click on the 'select value from' field and click on a cell in the report. The value gets populated automatically. Click 'Apply'.

Selecting data from the report

2.3. The formatting is applied to all the hierarchy levels where 2021 Actuals are greater than the selected value.

Conditional formatting based on data selection

### 3. Value You can apply conditional formatting based on another measure in the visual. Let's highlight 2021 Actuals when it is greater than the 2021 Plan. {% hint style="info" %} Measures that were added from the data source, auto-calculated variances, columns/measures created using calculations, data input and simulations can all be used in the IF condition. {% endhint %} 3.1. Click on the highlighted dropdown and select 2021 Plan. Click 'Apply'.

Conditional formatting based on another measure

3.2. Note that conditional formatting is applied only to the totals - Categories, Regions, and Subregions since we have chosen 'Row hierarchy levels' as 'Totals' and included 'Row grand total'.

Conditional formatting when 2021 Actuals > 2021 Plan

### 4. Formula You can create simple formulas using either numeric values or measures. Let's highlight the records where 2021 Actuals are greater than the 2021 Plan by at least 5m. Configure as shown in the below image and click 'Apply'.

Conditional formatting based on formula

Tea & coffee in the East subregion is the only record that matches the given condition.

Conditional formatting based on formula

The '**Add condition**' option lets you create nested AND/OR conditions. Let's consider another example where the conditions are Q4 2021 Actuals greater than 10m, the Category is Beverages and the Sub-regions are Pacific and East. **STEP 1:** Configure the settings as shown below for the first condition. Click on 'Add condition'.

Nested conditions

**STEP 2:** You can see another condition with default selections and an option to select AND/OR. Let's select 'AND' and 'Category' from the highlighted dropdown.

Adding an AND condition

**STEP 3:** You can see several options as shown in the below image. Let's go with the default option.

Options for category

**STEP 4:** In the 'Choose members' dropdown, select 'Beverages'.

Selecting a category

**STEP 5:** Let's now add the third condition. Click on 'Add condition'. In the highlighted dropdown (2021 Plan), select 'Sub Region'.

Adding a third condition

**STEP 6:** Select Pacific and East in the dropdown as shown below. Click 'Apply'.

Selecting multiple regions

**STEP 7:** You can see that the rows for Juices and Soda for East and Pacific are highlighted based on the three conditions.

Conditional formatting based on nested conditions

### 5. User selection You can apply conditional formatting based on a selection during runtime ie. in the reading view. Let's consider a case where we want conditional formatting to be applied to 2021 Actuals based on a Variance value selected during runtime. **STEP 1:** Configure as shown in the below image. Note that 2021 Actuals is set in the 'Apply to' field and 'Variance' in the IF condition. Click on the dropdown and select 'Variance'.

Conditional formatting based on user selection

**STEP 2:** Let's also add an icon. Click on the 'Icon' checkbox. There are several customization options.

Adding an icon

**STEP 3:** Click on the icon dropdown and choose the flag icon.

Customizing the icon

**STEP 4:** Change the flag color to green and the font color to black. Click 'Apply'.

Customizing the icon color

**STEP 5:** Click on a value in any of the variance columns. You can see flag icons in the 2021 Actuals column where the variance is greater than the selected variance.

Conditional formatting based on user selection

### 6. Dates With Plan, you can compare date dimensions in rows and columns and automatically format cells based on the comparison. Let's add date dimensions in the row and column parameters. To compare two date dimensions, select the **Compare Date** option. In the example below, we used conditional formatting to highlight the cells where the order date exceeds the ship date.

Compare date dimensions

In the example above, we compared each ship date against each order date and highlighted the cells with the order date greater than the ship date. Instead of comparing each cell, you can also compare a date dimension with the minimum or maximum value of another date dimension. To demonstrate this, let's highlight the cells with the order date greater than the minimum ship date.

Highlight cells with order date greater than minimum of ship date

You can compare date dimensions against static dates using the Selected Date option and specify the date to be compared against.

Compare against static dates

### 7. Conditional formatting for data input fields Highlight your data input fields like number, dropdown, and person columns with conditional formatting. When users enter values, you can specify rules to automatically apply formatting when the rules are satisfied e.g. spotlight the cells when the budget entered exceeds a certain limit.

Conditional formatting for data input fields

### 8. Rules for non-numeric measures You can apply conditional formatting to non-numeric measures using conditions like **is blank/is not blank/contains/does not contain,** etc. In this example, we've set a hatched background for the cells with blank customer names.
In the next section, we'll be covering [Color scale](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings/color-scale). # Color scale & data bars Another commonly used type of conditional formatting is the 'color scale,' which applies a gradient fill to a range of cells based on their values. This is a useful way to quickly visualize and compare values in a table. Color scale can be applied for the background, font color or data bars. It can also be applied row-wise, column-wise, or table-wise. Refer to [create rule](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings) to get started. Once the rule is created and you can see the 'Conditional formatting' side panel, follow the below steps. Choose 'Color scale' in the 'Format by' dropdown. There are several options enabled as shown in the below image.

Choosing color scale

## 1. Font/Background Let's go with the default. Click 'Apply'.

Basic color scale

### i. Based on a) Let's now apply formatting for 2021 Actuals based on the absolute variance between AC and PL. Select '2021 Actuals - 2021 Plan' from the 'Based on' dropdown. Click 'Apply'.

Conditional formatting based on a different measure

b) Let's hide the 2021 Plan column and show the variance column. Click on 'Manage columns' and check/uncheck the checkboxes as highlighted.

Enabling the 2021 Actuals - 2021 Plan column

c) You can see that the color scale has been updated to highlight values based on the variance. For eg., even though East -> Tea & coffee is 3.75m, the formatting is dark blue because of the high variance of +3.99m.

Conditional formatting for 2021 Actuals based on absolute variance AC vs PL

### ii. Color scale for a) Let's now apply color scale for font. Select 'Font' in the 'Color scale for' dropdown. Click 'Apply'.

Color scale for font

b) You can see that the font for 2021 Actuals is formatted sequentially based on the variance.

Color scale for font

c) Let's revert back to background formatting based on 2021 Actuals. Data bars are covered in the [next section](#2.-data-bars).

Background formatting

### iii. Heat map type a) Notice that the formatting is applied column-wise. That is the maximum and minimum values in a column are formatted using the darkest and lightest gradients.

Column wise formatting

b) Also, notice the value of East -> Soda across quarters. The highlighted values are around 15m, but for Q4 it is a darker gradient compared to the first two quarters. This is because of the column-wise formatting.

Column wise formatting

c) Let's now apply row-wise formatting. Select 'Row wise' in the 'Heat map type' dropdown and click 'Apply'. You can see that the maximum and minimum values in a row are formatted using the darkest and lightest gradients.

Row wise formatting

d) Also, notice the highlighted values in Q1. East -> Mineral water(16m) is greater than Pacific -> Soda (10m) but is formatted using a lighter gradient. And East -> Juices and Pacific -> Soda are formatted using the same gradient even though there is a huge difference. This is because of the row-wise formatting.

Row wise formatting

e) The below image shows table-wise formatting. Notice that similar values are formatted using the same gradient throughout the table.

Table wise formatting

### iv. Color scale type There are a number of types such as Sequential, Diverging, Qualitative, Continuous, etc. You can click on the 'Color scale type' dropdown to see the entire list. a) Let's apply a 'Diverging' color scale. Select from the dropdown and click 'Apply'.

Color scale types

b) You can see that the backgrounds are formatted using the Diverging scale.

Diverging color scale

c) Below is an example where the Continuous-Range scale is used.

Continuous-Range scale

### v. Colors #### a) Color scheme For color scale of types Sequential, Diverging, Diverging-color safe, Qualitative and Qualitative-color safe, there are several default color schemes to choose from. You can click on the 'Color scheme' dropdown and choose the desired color scheme.

Color schemes

#### b) Min/max/center For color scale of types Continuous-Range, Continuous-Diverging Range, Continuous and Continuous-Diverging, you can define the min/max colors and center color if applicable.

Color selection

#### c) Custom color scale The 'Custom' color scale type can be used to define custom color ranges based on value or percentage. On selecting the custom option, you can see the fields highlighted in the below image. You can define the values/percentages for the ranges, add or delete ranges, define colors and reverse the order. Changes can also be reset to default.

Custom color scheme

#### d) Reverse color Color scale can be reversed for cases where the minimum and maximum values need to be denoted with the highest and lowest gradients. To achieve this, check the 'Reverse color' checkbox.

Reversing color scale

#### e) Number of bands By default, the number of bands is defined as 5. But it can be increased or decreased using the 'Number of bands' field. The color scheme field also gets updated to show the gradients.

Number of bands

#### f) **Hide value** You can choose to show only the color scale and hide the values. To do this, check the 'Hide value' checkbox.

Hiding values

#### g) **Auto font color** When you apply background conditional formatting in Planning sheet, the font colors are automatically adjusted to be in contrast with their backgrounds to enhance readability. But it can be turned off if not required by unchecking the 'Auto font color' checkbox.

Auto font color disabled

#### h) **Include null** In cases where there are null values in your table, you can choose whether to include or exclude them from conditional formatting. In the below image, the 2022 Forecast for Pacific is empty. To show conditional formatting for Pacific as well, check the 'Include null' checkbox.

Conditional formatting for null values

## 2. Data bars In the previous section, we covered color scales for fonts and backgrounds. In this section, we'll look at data bars. Note that you can either create a new rule as shown here or edit the data bars inserted using the one-click option explained [here](https://docs.fabricplan.com/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings/pages/m52TtDtb8D33x7Nzkrv9#4.-data-bars). a) Select 'Data bars' in the 'Color scale for' dropdown.

Selecting color scale for data bars

b) In the side panel, you can see the customization options available.

Data bar properties

c) Let's go with the default settings. Click 'Apply'. The data bar gets added as shown in the below image.

Data bar added for 2021 Actuals

d) Let's now look at each of the customization options. Check the 'Hide value' checkbox and click 'Apply'. You can now see only the data bars and the values are hidden.

Hiding the values

e) By default, the data bars are applied to all the values (values and totals if selected in the row hierarchy levels). If you only want to use data bars for a particular range of values, you can enter a minimum and maximum value as shown in the below image. Data bars are enabled only for values between 8m and 20m in this case.

Data bars for a min-max range

f) Data bars can be aligned left or right using the 'Alignment' field. Right alignment is shown in this example.

Data bars alignment

g) Note that when there are positive and negative values in the column for which you want to use data bars, the options differ as shown in the below image. You can choose to hide the values and select colors for positive and negative values.

Data bars for positive and negative values

In the next section, we'll take a look at [Classification](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings/classification). # Classification Learn how to use classification-based conditional formatting - such as text, icons, and ratings - to quickly gain insights from your reports. ### What is classification-based conditional formatting? {% hint style="success" %} Classification in Plan is a conditional formatting feature that allows you to categorize data based on value ranges. It helps visually segment data and analyze performance in spreadsheets or tabular reports. Real-world reporting involves highlighting outliers and identifying key trends. With Plan, you can categorize data using text, icons, or ratings, making it easy to interpret complex data at a glance. {% endhint %} ### Configuring classification-based formatting Plan provides data classification based on text, icons, and ratings. Refer to [create rule](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings) to get started. Once the rule is created and you can see the **Conditional Formatting** side panel, follow the steps. Choose **Classification** in the **Format by** dropdown. Click on the **Display** dropdown. You can see the three types of classifications already mentioned. Let's look at them one by one.

Data classification options

#### 1. Text Many times, it may become essential to categorize performance. A common technique used by enterprises to categorize inventory, vendors, or materials is [ABC analysis](https://en.wikipedia.org/wiki/ABC_analysis). Plan delivers this capability right out of the box. 1.1. Select **ABC** from the **Display** dropdown. Let's go with the default settings. Click Apply.

ABC analysis

1.2. The rows are classified, and the text is displayed on the left of the data.

ABC analysis

1.3. You can also have the classifications appear in a separate column if required. Check the **Show as new column** checkbox.

Display text in new column

1.4. You can choose to enable a background for the text. The **Enable background** option is only available for text classification, not icons or ratings.

Adding a background color for text classifcation

1.5. You can also choose to display only the classification text by selecting ***Only Text*** from the **Text position** dropdown. This image shows a different type of text classification with longer performance indicators like LOW/VERY LOW/HIGH, etc.

Display only the classification without the actual measure value

1.6. When you choose the *Only Text* option, you can choose to apply the color to the font, background, or both.

Apply classification colors to the cell background

1.7. You can display the percentage contribution to the left or right of the text using the **Percentage text** dropdown.

Display percentage contribution along with the classification

1.8. You can define custom ranges as shown below. Click the **Add Range** button to create an additional range of values.

Defining classification ranges

1.9. Ranges can also be defined using numeric values. Click on the **Value** option. The values get populated automatically based on the defined percentages, but they can be further edited. You can delete specific ranges or all changes using the icons highlighted in the image.

Using numeric value ranges

#### 2. Icon set Icon-based formatting provides the same properties as text-based formatting. Refer to [Text](#1.-text) for more details. We'll look at a simple example for icon sets. In the **Display** dropdown, choose the **Five arrows** option. Click Apply. Note that we have applied formatting based on PY revenue. Hence, we can quickly gauge the performance of the previous year's revenue.

Icon based classification of tabular data

#### 3. Rating You can also use star-based ratings as seen in e-commerce sites in the product feedback section. **STEP 1:** Select the **3 stars** option from the **Display** dropdown and click Apply.

Conditional formatting - Rating

**STEP 2:** You can see the 1-3 rating icons on the left of the data. By default, the number of ranges has been set to 5.
**STEP 3:** You can move the ratings to a separate column by clicking on the **Show as new column** checkbox.

Ratings in a new column

#### **3.1. Invert rating** Check the **Invert rating** checkbox in cases where higher values need to show lower ratings.

Inverted ratings

As seen in text and icon-based formatting, you can choose to display only the classification or change the position. In this example, we have chosen the **Only rating** option.

Displaying only rating

#### **3.2. Number of range**s You can customize the number of icons that appear in your scale. The number of ranges has been set to 5 in this case.

Changing the number of ranges

The rating text can be shown on the left or right. Select the position and color as shown below.

Rating text

#### **3.3. Enable custom** In Plan, you have the option to configure value ranges for each rating. Click on the **Enable Custom** checkbox. For every half rating, you'll be able to define percentage or value ranges in the **From** and **To** fields, as shown in the image below.

Custom ranges

#### **FAQs** **Q1: Can I customize the ranges and display options for classification?**\ Yes. Plan allows you to set custom value ranges, choose how classifications are displayed (text, icons, ratings), and decide whether to show classification in a new column or inline. You can also adjust colors and positions to match your reporting needs. **Q2: How does classification handle dynamic data changes?**\ Classification in Plan is dynamic. When your underlying data changes, the classification automatically updates based on your defined ranges and rules. This ensures that trends and outliers are always accurately highlighted in your reports. **Q3: Does applying classification affect report performance?**\ Applying classification does not impact report performance. It does not affect data refresh, calculations, or visual rendering. # Ranking Plan highlights the top or bottom N ranked values, making it easy to quickly identify the highest- and lowest-performing items. One common use of conditional formatting is to highlight the top/bottom N-ranked items in a report, which can help to quickly identify the most important or relevant items in the data. Refer to [create rule](/documentation/readme/planning-sheets/how-tos/adding-business-logic-and-formulae/5.-conditional-formatting/create-rule-basic-settings) to get started. Once the rule is created and you can see the **Conditional formatting** side panel, follow the steps below. Choose **Ranking** in the **Format by** dropdown. You can see the options shown in the image below. Using the **Style** section, you can format text based on color/style, apply a background color, or select icons.

Conditional formatting based on Top N ranking

**STEP 1:** Let's apply icon-based formatting to the Top 3 variants based on Jan 2025 revenue. Update the highlighted properties as shown below and click **Apply**.

Customizing the rule

**STEP 2:** The top 3 categories based on Jan 2025 PY Revenue Recalc show an icon on the left and are highlighted in blue, as shown below.

Conditional formatting applied based on Top N ranking

**STEP 3:** You can also apply ranking based on **percentages.** In the image, the bottom 50% of variants based on Jan 2025 PY Revenue Recalc are highlighted in blue.

Conditional formatting for bottom 50% of variant

**STEP 4:** You can apply conditional formatting for the top and bottom items using the **Both** option. In the image, conditional formatting has been applied for the top and bottom variants.

Conditional formatting for top and bottom variants

**STEP 5:** Let's create one more rule for highlighting the top 5 brands, i.e., the subtotal rows. Create a new rule and configure it as shown below. Click Apply.

Applying CF for brand based on Ranking

**STEP 6:** Conditional formatting is not applied because it has been configured for **Values only.**

Conditional formatting for values

**STEP 7:** Change the Row hierarchy levels to **Values and totals** and click Apply. Notice how the subtotals are updated.

Conditional formatting for values and totals

#### **FAQs** **Q1: Can I customize the ranking criteria and display options?**\ Yes. Plan allows you to define rankings based on a selected measure, choose top or bottom *N* values, and control how ranked items are highlighted using conditional formatting options. **Q2: How does ranking handle dynamic data changes?**\ Ranking in Plan is dynamic when underlying data changes; rankings are automatically recalculated based on the defined criteria, ensuring accurate and up-to-date results. **Q3: Does applying ranking affect report performance?**\ No. Applying ranking does not impact report performance, data refresh, or calculations. --- [Next Page](/llms-full.txt/1)