Recreating Excel‑Like Heat Maps in SAP Analytics Cloud Using Dynamic User‑Driven Thresholds

[zee_siddique15]

If you’ve worked with Excel heat maps, you already know how effortless it feels.

Just select your data → apply Conditional Formatting → Color Scales, and instantly you get a beautiful gradient showing highs, lows, and patterns. The colors automatically react to the values simple, intuitive, and powerful.

Excel :- 

But when you try to replicate the same behavior in SAP Analytics Cloud (SAC), things start to get interesting.

The Challenge in SAC

 In SAC, you can create heat maps and apply conditional formatting using thresholds. However:

Heat map charts rely on a single measure for color intensity
Conditional formatting in tables works with:
      -  Number ranges (static) OR
     -   Comparison to another measure (dynamic)
The real limitation comes when:  You want the gradient to adapt dynamically based on user-defined thresholds, like min/mid/max inputs similar to Excel flexibility.

Out-of-the-box, SAC doesn’t directly provide this level of interactive gradient control in a native heat map.

 So the question becomes:

Can we mimic Excel’s behavior using SAC building blocks?

Yes, and that’s exactly what this solution does.

 Solution Overview

 Instead of relying on the native SAC heat map, the approach shifts to:

          Using a Table + Calculated Measure + Conditional Formatting

This gives us full control over how colors are applied

 Step 1: Design the Data Structure

In the main SAC table:

Measure X → Holds the actual values (the data you want to visualize)
Values are stored at specific intersections (e.g., based on dimensions like Bay, Post, etc.)
Additionally, create 3 input measures:

• Minimum
• Midpoint
• Maximum

These are not part of the main data grid, but stored at separate intersections where users can input values

Step 2: Enable User Input for Thresholds

Users enter:

•  Minimum threshold
•  Midpoint threshold
•  Maximum threshold

This allows complete flexibility; users can dynamically control how the gradient behaves based on their scenario.

Step 3: Create the Logic – “XColorScore”

1) Created a Restricted Measure "RM-X" restricted  to its intersection at a a story level 

2) Created 3 Restricetd Measures one for each Measure restricted to its intersection at a story level

•  Minimum (RM - Min)
•  Maximum (RM - Max)
•  Midpoint (RM - Mid)

3) Create a calculated measure (XColorScore) that:

Compares each Measure X value against Minimum, Midpoint, and Maximum

Example logic (conceptually):

If X <= Minimum → assign lower score
If X ≈ Midpoint → assign medium score
If X >= Maximum → assign higher score
Essentially, you convert your raw values into a color scoring scale.

This follows the SAC best practice of deriving a numeric output to drive conditional formatting

Step 4: Apply Conditional Formatting

Now comes the clever part.

In SAC:

• Select Measure X 
• Go to Conditional Formatting (Thresholds)
        Choose:  Compare To → Measure 
                          Comparison Measure → XColorScore

Since thresholds in SAC can compare one measure against another, this enables dynamic, data-driven coloring logic instead of static ranges.

Step 5: Achieve the Gradient Effect

By defining appropriate ranges in the threshold:

Green → lower values
Yellow → mid-range
Red → higher values
You effectively simulate:

•  A heat map
• A gradient scale
• Based on user inputs

Why This Approach Works

This design leverages three powerful SAC concepts:

Separation of Data & Logic
→ Raw data (X) vs Colour logic (XColorScore)
User-driven input
→ Thresholds are not hardcoded
Measure-to-measure comparison
→ Unlocks dynamic conditional formatting

 Key Takeaways

Native SAC heat maps are great, but not always flexible enough
Conditional formatting with measure comparison is the real game changer
By introducing a scoring layer, you can simulate Excel-like gradients
Most importantly:  You give control back to the business user

Closing Thoughts

Moving from Excel to SAC often exposes these small gaps in flexibility.

But with a bit of creative modelling, SAC can go beyond Excel,especially when you combine:

• Planning input
•  Calculated measures
•  Conditional formatting

This solution is a perfect example of that.