Use Case Scenario: Creating and Filtering an Overspend Report for Live Campaigns

Goal: Create a Total Spend Report with a custom formula for live campaigns that overspent in the current year.

In the Layout Configuration section, add the following data fields to create the spend report:

  • Campaign Name and Campaign ID fields to the Rows section
  • Total Spend (Gross Contracted) and (Gross Actual) fields to the Measures section

The Total Spend fields show the contracted and actual (delivered) spend. Create the overspend calculated measure with the Total Spend fields.

The procedures and images assist you in creating your custom formulas and filters.

Live Campaign with Total Spend Report

Total spend report for a live campaign with measures highlighted

To create an Overspend Report for Live Campaigns:

  1. Go to Main Menu [] > Insights.

  2. Select Total Spend (Gross Actual) > User Defined Measure > Create Calculated Measure in the Layout Configuration.

  3. Enter a Display Name, such as Overspend.

  4. Select a Format.

  5. Select the number of Decimal Places, such as 2.

  6. Click Clear. The column to the right of this modal clears of any metric MDX (formula(s)).

  7. Double-click Total Spend (Gross Actual). The MDX formula appears in the column to the right of the modal.

  8. Double-click Total Spend (Gross Contracted). The MDX formula appears in the column to the right of the modal.

    Create Calculated Measure modal with selected measures highlighted

  9. Enable the Calculate subtotals using measures formula option.

  10. Click OK. You can see the Overspend column in the far right side of the report table.

    Example report with the Overspend column highlighted

The new spend report shows all dollar amounts including $0.00.

You create a filter to show the positive amounts greater than $0.00 in the new Overspend column.

To filter an Overspend Report for Live Campaigns:

  1. Click Overspend in the Layout Configuration.

  2. Select Greater/Less Than, Equal To, etc. The numeric filter modal appears.

    Overspend column options with Greater/Less Than, Equal To, etc. highlighted

  3. Confirm the Filter on selection is Campaign ID. (The Campaign ID shows by default.)

  4. Enable the Greater/Less Than, Equal To, etc. option.

    Numeric Filter modal with selected filter highlighted

  5. Confirm the Overspend filter field.

  6. Confirm the Greater Than filter field.

  7. Enter zero (0), in the last filter field

  8. Confirm the Top 10, etc. option is clear.

  9. Click OK. The new Overspend field appears in the report excluding the $0.00 values.