5 - Build a P&L hierarchy with Measure Model

In this tutorial, you will build and configure a Measure Model by organizing native and formula measures into a hierarchical P&L structure, then create two scenarios in the Tree layout and compare them to see the real-time impact of revenue growth and cost restructuring on Net Profit.

Prerequisites #

Before you start this tutorial, make sure to complete the first three tutorials:

• Introduction to planning in Microsoft Fabric
• Allocations and collaboration
• Optimize input values

This tutorial uses the plan app and the Fabric SQL database you created in the previous tutorials.

Configure a measure model #

In this section, you set up the planning sheet and build a simple P&L model using the Measure Model feature. Organize native measures into a parent-child hierarchy using formula measures to turn a flat list of inputs into a structured P&L model.

1. In Northwind_FMCG_Plan, select New Planning Sheet in the Home ribbon. Enter P&L – Measure model and select Create.
2. Configure the field assignments from the P&L Measures table as follows:

3. Double-click each measure label and remove the "Sum of" prefix to improve readability. Select the three dots (⋮) next to Avg Selling Price in the Values field and select Average from the dropdown. This populates the average selling price instead of the summed value.

4. Navigate to the Model ribbon and select Measure Model. The measure model canvas opens.
5. To create a formula measure for Gross Revenue, first drag Sales Volume and Avg Selling Price into the Drop from measure list section at the bottom of the canvas.
6. Select Add Measure > Formula. Name it Gross Revenue and enter the formula [Sales Volume]*[Avg Selling Price]. Select Create.

• Gross Revenue is added to the measure list.

7. Drag the new Gross Revenue measure onto the canvas. Drag the Sales Volume and Avg Selling Price onto the Gross Revenue row — the measures snap into place as children. Observe that Gross Revenue becomes the parent of Sales Volume and Avg Selling Price.

8. Drag Discounts and Returns onto the canvas. Follow the same steps to create a Net Revenue measure by using the formula [Gross Revenue] – [Discounts and Returns].
9. Drag Net Revenue onto the canvas. Then drag Gross Revenue and Discounts and Returns under the Net Revenue row. Observe that Net Revenue becomes the parent of Gross Revenue and Discounts and Returns.

10. Create a formula measure for COGS by using the formula: [Raw Material Cost]+[Labor Cost]+[Other Direct Exp]. Drag the COGS measure onto the canvas.
11. Select Raw Material Cost, Labor Cost, and Other Direct Exp. From the Insert Measure dropdown, select COGS, then select Insert.

The COGS measure becomes the parent of Raw Material Cost, Labor Cost, and Other Direct Exp.

12. In the same way, create the following measures:

The final pnl hierarchy is shown in the following image:

View the P&L Model #

In this section, you will view the P&L model in the Measures in Rows layout.

1. Select Back to Home at the top of the measure model canvas. In the Planning ribbon, select the Layout dropdown and select Measures In Rows.
2. Observe that the configured measure hierarchy is now displayed — a structured P&L view of the calculated measures and their underlying components, replacing the flat list from the setup step.

Create best case and cost restructuring scenarios #

In this section, you create two scenarios in the Tree layout — one to simulate revenue growth in Sydney and one to simulate restructuring costs in London.

Create best case scenario

In this section, you create a Best Case scenario in the Tree layout and simulate revenue growth in Sydney. The measure hierarchy updates in real time as you adjust each input.

1. In the Planning ribbon, select Layout and select Tree. The P&L model displays in tree view, breaking down from the grand total through region and country with the measure hierarchy.

2. Select Display in the Tree View ribbon. In the Display Settings side panel, turn off Show Header KPI.
3. Select Create Scenario and name it Best Case. Ensure that all measures from the semantic model are included in the series, and click Create.

4. Under the Asia Pacific region, select the Sydney card. The simulation dialog box opens.
5. In the Measure Simulation dropdown, simulate the following under Gross Revenue:

• Sales Volume: 5%
• Avg Selling Price: 2%
• Discounts and Returns: −5%

8. Close the simulation dialog. Observe that the Sydney card updates in real time — Net Profit, Gross Profit, and Net Revenue show the upward variance indicators as each input is adjusted. The Asia Pacific and the All cards also update to reflect the change.

Cost Restructuring scenario

In this section, you create a Cost Restructuring scenario and simulate reduced costs in London. This scenario lets you compare a cost-focused approach against the Best Case revenue-growth approach.

1. Select Create Scenario and name it Cost Restructuring, ensure that all measures from the semantic model are included in the series, and select Create.
2. Confirm you can see the new scenario in the bottom left. Under the Europe region, select the London card. The simulation dialog box opens. Select Value. Scroll down and simulate the following under COGS and Operating Expenses:

• Raw Material Cost: 400k
• Labor Cost: 150k
• Employee Expenses: 120k
• Selling and Marketing Expenses: 160k

3. Close the simulation dialog box. Select the down arrow on the London card and Europe cards. Observe that the cards update in real time — the full P&L cascade is visible on the card, showing how the restructured cost inputs flow through COGS, Operating Expenses, Gross Profit, and Net Profit.

Compare Scenarios #

In this section, you compare the Best Case Scenario with the Cost Restructuring scenario by using the Compare Scenarios feature.

1. In the Tree View ribbon, select Compare Scenario.
2. Configure the comparison with the following settings:

• Compare: Best Case
• With: Cost Restructuring

3. Observe the variance columns — the compare view places both scenarios side by side at every level of the hierarchy, making the difference immediately visible between a revenue growth approach and a cost restructuring approach.

4. Click Exit Compare to return to the tree view.