High-Margin Job Mix % (by Booked Revenue):
Heatmap by Channel

High-Margin Job Mix % (by Booked Revenue)

1. Confirm the Required Columns in Your Dataset

• Make sure your table includes at minimum:
  • Date/Time
  • Booked Revenue
  • Gross Margin percent
  • Gross Profit
  • Marketing Channel

2. Create the 3 Helper Columns in the Data Table

• Add three new columns to your dataset:
  • Margin Flag
  • Month
  • Year

3. Build the Margin Flag as a Binary Trigger

• In Margin Flag, create an IF statement that returns:
  • 1 if the job margin is greater than your threshold
  • 0 otherwise
• Example logic covered in the video:
  • If Gross Margin % > 0.5 then 1 else 0
• Important: the threshold should eventually point to a changing cell on your dashboard tab so the entire model recalculates when the threshold changes.

4. Extract Month and Year From the Date

• Use a TEXT function to create Month:
  • Convert the date into a month label (example shown: “Jan” style month output)
• Use a TEXT function to create Year:
  • Convert the date into a 4-digit year (YYYY)

5. Create a Unique List of Month-Year Values

• Use UNIQUE on the Month-Year field so you have one row per month-year
• Copy and paste values to remove formulas:
  • Ctrl + C
  • Ctrl + Shift + V

6. Create a Unique, Sorted, Transposed List of Marketing Channels

• Build the channel headers across the top using a single nested formula approach:
  • UNIQUE to get channels
  • SORT to alphabetize
  • TRANSPOSE to put them across columns
• Copy and paste values (Ctrl + Shift + V) so the header row stays stable.

7. Calculate High-Margin Booked Revenue With SUMIFS

• In the heat map grid, calculate high-margin booked revenue for each intersection of:
  • Month-Year row
  • Channel column
• Use SUMIFS where:
  • Sum range = Booked Revenue
  • Criteria include:
    • Margin Flag = 1
    • Month = selected month
    • Year = selected year
    • Channel = selected channel
      
      

8. Divide by Total Booked Revenue for the Same Month and Channel

• Create the denominator as another SUMIFS:
  • Sum range = Booked Revenue
  • Criteria include:
    • Month
    • Year
    • Channel
  • Do not include Margin Flag in the denominator
• Final KPI cell logic:
  • High-margin booked revenue ÷ total booked revenue

9. Fix Relative Referencing Before You Fill the Grid

• Add dollar signs to lock the correct row and column references so the formula fills properly:
  • Lock month and year cells so they do not shift when dragged across
  • Lock the channel header row so it does not shift when dragged down
• Do not rely on dragging a structured table reference that changes columns unexpectedly
  • Copy the corrected formula first, then fill across and down

10. Handle Divide-by-Zero and Empty Intersections

• Wrap the full formula in IFERROR
• In the video, the preferred output is a dash:
  • If error, return “-”
  • Otherwise return the percentage

11. Add the Overall Column

• Create an “Overall” column to show the high-margin mix by channel regardless of month
• Copy the existing fraction formula and remove:
  • Month criteria from numerator and denominator
  • Year criteria from numerator and denominator
• Keep:
  • Channel criteria
  • Margin Flag criteria in the numerator only
• Format as percent (Ctrl + Shift + 5)

12. Add Two Changing Cells: Threshold and Target

• Add two input cells on the dashboard:
  • High Margin Threshold (this drives the Margin Flag)
  • Target Percentage (this drives conditional formatting)

13. Tie the Margin Flag to the Threshold Cell

• Update the Margin Flag formula so it points to the threshold input cell
• Lock the threshold reference so it stays fixed
• Once connected, changing the threshold instantly recalculates the entire heat map.

14. Add Conditional Formatting for Target Performance

• Select the full heat map plus the Overall column
• Create a rule:
  • Highlight cell rules
  • Less than
  • Reference the Target Percentage input cell
• Set the formatting color for under-target cells (example in the video: red)
• Result:
  • The heat map updates based on both the threshold and the target