Please explain how to create a revenue and expense waterfall report using power pivot…
- User
Please explain how to create a revenue and expense waterfall report using power pivot with no chart.
Assistant
Of course. Creating a revenue and expense waterfall report without a chart is an excellent use of Power Pivot. The goal is to create a table that shows a running total, calculating key subtotals like Gross Profit and Net Income along the way.
The core of this technique lies in creating a DAX measure that calculates a running total based on a predefined sort order.
Here is a detailed, step-by-step guide.
The Concept: What We're Building
A standard waterfall chart shows how a starting value is affected by a series of positive and negative changes. We will replicate this logic in a PivotTable.
Our final report will look something like this:
Account Amount Waterfall Gross Revenue $1,000,000 $1,000,000 Cost of Goods Sold (COGS) ($400,000) $600,000 Gross Profit $600,000 $600,000 Sales & Marketing ($150,000) $450,000 Research & Development ($100,000) $350,000 General & Administrative ($80,000) $270,000 Operating Income $270,000 $270,000 Interest & Taxes ($70,000) $200,000 Net Income $200,000 $200,000 Notice the
Waterfallcolumn. For standard items, it's a running total. For subtotal items (like Gross Profit), it shows the subtotal itself. This is the key we'll build with DAX.
Step 1: Prepare Your Source Data
This is the most critical step. Your data needs to be structured properly in an Excel Table to control the waterfall's logic.
-
Create a table in Excel with at least these four columns:
Account: The name of the revenue or expense line item (e.g., "Gross Revenue").Amount: The value. Important: Use positive numbers for revenue and negative numbers for expenses.SortOrder: A number column that defines the exact order of calculation. This is non-negotiable for a waterfall.Type: A text column to distinguish between calculation items and subtotals. Use "Detail" for items to be added/subtracted and "Subtotal" for the summary lines.
-
Populate the table. For our example, the data would look like this. Make sure it's an official Excel Table (
Insert > TableorCtrl+T). Let's name the tableFinancials.
Account Amount SortOrder Type Gross Revenue 1000000 1 Detail Cost of Goods Sold (COGS) -400000 2 Detail Gross Profit 600000 3 Subtotal Sales & Marketing -150000 4 Detail Research & Development -100000 5 Detail General & Administrative -80000 6 Detail Operating Income 270000 7 Subtotal Interest & Taxes -70000 8 Detail Net Income 200000 9 Subtotal Note on Subtotals: The
Amountfor the "Subtotal" rows is pre-calculated. For example, theGross Profitamount (600,000) is the sum of the amounts forSortOrder1 and 2. This structure gives you full control over the report's layout.Step 2: Load the Data into Power Pivot
- Click anywhere inside your
Financialstable. - Go to the Power Pivot tab in the Excel ribbon.
- Click "Add to Data Model".
This will open the Power Pivot window with your
Financialstable loaded.Step 3: Create the DAX Measures
This is where the magic happens. We'll create two measures.
-
Base Measure for Amount: It's best practice to always create an explicit measure for any value you want to calculate. In the calculation area (the bottom pane of the Power Pivot window), click in a cell and type the following formula:
Total Amount := SUM(Financials[Amount])Press Enter. This creates a measure that simply sums up the
Amountcolumn. -
The Waterfall Measure: This is the core measure. It will check if a row is a "Detail" or "Subtotal" and behave differently for each.
Click in another cell in the calculation area and enter this DAX formula:
Waterfall Value := VAR CurrentSortOrder = MAX( Financials[SortOrder] ) VAR CurrentType = SELECTEDVALUE( Financials[Type] ) RETURN IF( CurrentType = "Subtotal", [Total Amount], CALCULATE( [Total Amount], FILTER( ALL( Financials ), Financials[SortOrder] <= CurrentSortOrder && Financials[Type] = "Detail" ) ) )
Breaking Down the
Waterfall ValueMeasure:VAR CurrentSortOrder = MAX(Financials[SortOrder]): This variable captures theSortOrderof the current row being evaluated in the PivotTable.VAR CurrentType = SELECTEDVALUE(Financials[Type]): This captures theType("Detail" or "Subtotal") of the current row.IF(CurrentType = "Subtotal", ...): This is our main logic switch.- If TRUE (the row is a "Subtotal"): The formula simply returns
[Total Amount]. This just shows the pre-calculated subtotal value from our source table. - If FALSE (the row is a "Detail"): It performs the running total calculation.
- If TRUE (the row is a "Subtotal"): The formula simply returns
CALCULATE([Total Amount], ...): This calculates our base[Total Amount]measure but with a modified context.FILTER(ALL(Financials), ...): This is the context modifier.ALL(Financials): This part removes any existing filters on theFinancialstable, allowing us to look at all rows.Financials[SortOrder] <= CurrentSortOrder: This part keeps only the rows from the beginning up to and including the current row's sort order.&& Financials[Type] = "Detail": This is crucial. It ensures that our running total only sums the "Detail" items and ignores the "Subtotal" values, preventing double-counting.
Step 4: Build the PivotTable Report
- Close the Power Pivot window and go back to your Excel sheet.
- Go to the
Inserttab and click PivotTable. - In the dialog box, choose "Use this workbook's Data Model" and click OK.
- The PivotTable Fields pane will appear on the right, showing your
Financialstable and the measures you created (they have a calculator iconfx). - Drag the fields as follows:
- Rows: Drag the
Financials[Account]column here. - Values: Drag
[Total Amount]and[Waterfall Value]here.
- Rows: Drag the
Step 5: Sort and Format the Report
Your PivotTable is almost ready, but it's likely not sorted correctly yet.
-
Sort by
SortOrder:- Click on the
Accountcolumn in the PivotTable. - Go to
Data>Sort. - This is a common Excel limitation. A better way is to set the sort order in the Data Model itself.
- Best Practice: Go back to the Power Pivot window. Select the
Accountcolumn. In theHometab, click "Sort by Column". In the dialog, choose to sort theAccountcolumn by theSortOrdercolumn. Click OK. When you return to Excel, your PivotTable should automatically be in the correct order. If not, a simple refresh (Data > Refresh All) will fix it.
- Click on the
-
Final Formatting:
- Turn off Totals: Click inside the PivotTable. Go to the
Designtab >Grand Totals>Off for Rows and Columns. The "Net Income" line serves as our grand total, so the PivotTable's own total is redundant. - Number Formatting: Right-click on a number in the
Total Amountcolumn >Number Format...and chooseCurrencyorAccounting. Do the same for theWaterfall Valuecolumn. - Clarity: You can bold the subtotal rows using conditional formatting or by manually selecting and bolding the
Accountname (this formatting will stick).
- Turn off Totals: Click inside the PivotTable. Go to the
You now have a fully functional, refreshable waterfall report in a table format, driven entirely by Power Pivot. When your source data changes, just right-click the report and select Refresh.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.