Prompt Playbook: KPI Card with Background Trend + Scrollable Table
(Power BI HTML Content visual, built via DAX)
Use this sequence when you want to recreate the pattern from scratch — a KPI
card that shows a headline number, a background trend line, a vs.-prior
comparison badge, and a scrollable table of the underlying period-by-period
values, all in a single HTML Content visual.
Follow the prompts roughly in this order. Each one builds on the previous
measure, so paste the working DAX back in if you’re starting a new chat.
1. Establish the base comparison measure
“I want a KPI card that shows actual vs. last [period] and their variance —
[metric] and previous [period]’s [metric].”
Gets you: a measure with _Actual, _Prior, _Var, _VarPct variables.
2. Pick the output format
“I’m using the HTML Content visual (Regular edition) — give me the measure
as raw HTML.”
Say this up front and skip the back-and-forth: name your visual (e.g. “HTML
Content” from AppSource) and edition right away. Regular unlocks CSS and
animation; Lite sanitizes more strictly. Getting this right early avoids
building the wrong output format and having to redo it.
3. Add the background trend
“Can we also show [period] trend in the background?” → specify granularity:
“daily, non-cumulative” or “monthly” as needed.
Builds an ADDCOLUMNS/CONCATENATEX table of points, plotted as an SVG<polyline> behind the card’s text.
4. Add the scrollable table
“Can we add a small table under it, scrollable, showing [period] and
[value]?”
Wraps a <table> in a <div style="max-height:...px;overflow-y:auto;">,
reusing the same data table the sparkline already built — don’t duplicate the
query.
5. Fix the date/period labels
“It shows the wrong year” → “show month name and year” → “update this table
with month number” (for correct calendar sort order, since text month names
sort alphabetically by default).
6. Add year-level totals with month-level trend
“We also need yearly total” → “in the background, monthly trend.”
Two grains in one card: yearly headline number, monthly sparkline + monthly
table underneath.
7. Guard against misleading comparisons
“This is only one year of data” → “so green upward doesn’t make sense here.”
Add a _HasPriorData flag; show a neutral “No prior period to compare” badge
instead of a false increase/decrease.
8. Polish: number formatting
“Round to millions” (e.g. $8.4M instead of $8,423,838).
9. Polish: spacing
“Remove spacing in width and height so it uses minimal real estate.”
Cuts cell padding, font size, and row height for a denser table.
10. Polish: scrollbar
“Make the scrollbar thin and unobtrusive — only appears on hover.”
Adds a <style> block with ::-webkit-scrollbar rules scoped to a class name
on the scroll container.
Known pitfalls to mention up front (saves a round-trip)
- Long single-line string concatenations break in Tabular Editor’s editor
due to auto-wrap. Fix: breakRETURN "a" & "b" & "c"into aVARholding
the built string, thenRETURNthe var — keeps each line shorter. MonthPeriod-style integer columns: confirm whether they’re a realYYYYMMsequential key or just a repeating 1–12 month index before relying
on-1/-12arithmetic for “prior period.”- Per-point hover tooltips on an SVG sparkline are unreliable in HTML
Content (sanitizer strips<title>, z-index/stacking issues block the
hover target). A whole-cardtitle="..."attribute works fine — don’t
chase per-point tooltips unless you have time to spend on it. - DAX string literals need doubled quotes (
"") for HTML attributes,
e.g.style=""color:red;"".
One-shot version (if you already know what you want)
“Build an HTML Content KPI card measure for [metric]: headline = full year
total to millions ($X.XM), badge = vs. prior year variance and %
(gray ‘no prior year’ if only one year of data exists), background = last
12 months as an SVG sparkline, and a scrollable table underneath listing
each of those 12 months with formatted values, sticky header, thin
hover-only scrollbar.”
This single prompt reproduces the whole pattern in one pass, skipping the
incremental back-and-forth — useful once you’ve done it once and know the
shape you want.
Monthly-grain variant
Use this instead when the comparison should be month-over-month (latest
month vs. the month before it) rather than year-over-year — matching the
“left column” cards from the executive summary page.
“Build an HTML Content KPI card measure for [metric]: headline = latest
month’s total to millions ($X.XM), badge = vs. prior month variance and %
(color-coded green/red, or gray ‘no prior month’ if only one month of data
exists), background = last 12 months as an SVG sparkline, and a scrollable
table underneath listing each of those 12 months with formatted values,
sticky header, thin hover-only scrollbar.”
The only structural difference from the yearly version: anchor onDimDate[MonthPeriod] (or your model’s month-key column) for both the
headline and the comparison, using _LatestPeriod / _LatestPeriod - 1
instead of DimDate[Year] / _SelectedYear - 1. The background sparkline
and table stay the same either way since they were already monthly.