Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-pro· GoogleHugging Face LMArena Human Preference 140K

Please explain how to create a revenue and expense waterfall report using power pivot…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-3-5-sonnet-20241022’s answer.
  1. User

    Please explain how to create a revenue and expense waterfall report using power pivot with no chart.

  2. Gemini 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:

    AccountAmountWaterfall
    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 Waterfall column. 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.

    1. 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.
    2. Populate the table. For our example, the data would look like this. Make sure it's an official Excel Table (Insert > Table or Ctrl+T). Let's name the table Financials.

    AccountAmountSortOrderType
    Gross Revenue10000001Detail
    Cost of Goods Sold (COGS)-4000002Detail
    Gross Profit6000003Subtotal
    Sales & Marketing-1500004Detail
    Research & Development-1000005Detail
    General & Administrative-800006Detail
    Operating Income2700007Subtotal
    Interest & Taxes-700008Detail
    Net Income2000009Subtotal

    Note on Subtotals: The Amount for the "Subtotal" rows is pre-calculated. For example, the Gross Profit amount (600,000) is the sum of the amounts for SortOrder 1 and 2. This structure gives you full control over the report's layout.

    Step 2: Load the Data into Power Pivot

    1. Click anywhere inside your Financials table.
    2. Go to the Power Pivot tab in the Excel ribbon.
    3. Click "Add to Data Model".

    This will open the Power Pivot window with your Financials table loaded.

    Step 3: Create the DAX Measures

    This is where the magic happens. We'll create two measures.

    1. 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 Amount column.

    2. 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 Value Measure:

    • VAR CurrentSortOrder = MAX(Financials[SortOrder]): This variable captures the SortOrder of the current row being evaluated in the PivotTable.
    • VAR CurrentType = SELECTEDVALUE(Financials[Type]): This captures the Type ("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.
    • 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 the Financials table, 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

    1. Close the Power Pivot window and go back to your Excel sheet.
    2. Go to the Insert tab and click PivotTable.
    3. In the dialog box, choose "Use this workbook's Data Model" and click OK.
    4. The PivotTable Fields pane will appear on the right, showing your Financials table and the measures you created (they have a calculator icon fx).
    5. Drag the fields as follows:
      • Rows: Drag the Financials[Account] column here.
      • Values: Drag [Total Amount] and [Waterfall Value] here.

    Step 5: Sort and Format the Report

    Your PivotTable is almost ready, but it's likely not sorted correctly yet.

    1. Sort by SortOrder:

      • Click on the Account column 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 Account column. In the Home tab, click "Sort by Column". In the dialog, choose to sort the Account column by the SortOrder column. 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.
    2. Final Formatting:

      • Turn off Totals: Click inside the PivotTable. Go to the Design tab > 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 Amount column > Number Format... and choose Currency or Accounting. Do the same for the Waterfall Value column.
      • Clarity: You can bold the subtotal rows using conditional formatting or by manually selecting and bolding the Account name (this formatting will stick).

    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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.