{"id":25,"date":"2026-09-08T18:40:17","date_gmt":"2026-09-08T18:40:17","guid":{"rendered":"https:\/\/www.datascientist.ca\/blog\/?p=25"},"modified":"2026-09-08T18:40:19","modified_gmt":"2026-09-08T18:40:19","slug":"prompt-playbook-kpi-card-with-background-trend-scrollable-table","status":"publish","type":"post","link":"https:\/\/www.datascientist.ca\/blog\/prompt-playbook-kpi-card-with-background-trend-scrollable-table\/","title":{"rendered":"Prompt Playbook: KPI Card with Background Trend + Scrollable Table"},"content":{"rendered":"\n

Prompt Playbook: KPI Card with Background Trend + Scrollable Table<\/h1>\n\n\n\n

(Power BI HTML Content visual, built via DAX)<\/h3>\n\n\n\n

Use this sequence when you want to recreate the pattern from scratch \u2014 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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

\n\n\n\n

1. Establish the base comparison measure<\/h2>\n\n\n\n
\n

“I want a KPI card that shows actual vs. last [period] and their variance \u2014
[metric] and previous [period]’s [metric].”<\/p>\n<\/blockquote>\n\n\n\n

Gets you: a measure with _Actual<\/code>, _Prior<\/code>, _Var<\/code>, _VarPct<\/code> variables.<\/p>\n\n\n\n

2. Pick the output format<\/h2>\n\n\n\n
\n

“I’m using the HTML Content visual (Regular edition) \u2014 give me the measure
as raw HTML.”<\/p>\n<\/blockquote>\n\n\n\n

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.<\/p>\n\n\n\n

3. Add the background trend<\/h2>\n\n\n\n
\n

“Can we also show [period] trend in the background?” \u2192 specify granularity:
“daily, non-cumulative” or “monthly” as needed.<\/p>\n<\/blockquote>\n\n\n\n

Builds an ADDCOLUMNS<\/code>\/CONCATENATEX<\/code> table of points, plotted as an SVG
<polyline><\/code> behind the card’s text.<\/p>\n\n\n\n

4. Add the scrollable table<\/h2>\n\n\n\n
\n

“Can we add a small table under it, scrollable, showing [period] and
[value]?”<\/p>\n<\/blockquote>\n\n\n\n

Wraps a <table><\/code> in a <div style=\"max-height:...px;overflow-y:auto;\"><\/code>,
reusing the same data table the sparkline already built \u2014 don’t duplicate the
query.<\/p>\n\n\n\n

5. Fix the date\/period labels<\/h2>\n\n\n\n
\n

“It shows the wrong year” \u2192 “show month name and year” \u2192 “update this table
with month number” (for correct calendar sort order, since text month names
sort alphabetically by default).<\/p>\n<\/blockquote>\n\n\n\n

6. Add year-level totals with month-level trend<\/h2>\n\n\n\n
\n

“We also need yearly total” \u2192 “in the background, monthly trend.”<\/p>\n<\/blockquote>\n\n\n\n

Two grains in one card: yearly headline number, monthly sparkline + monthly
table underneath.<\/p>\n\n\n\n

7. Guard against misleading comparisons<\/h2>\n\n\n\n
\n

“This is only one year of data” \u2192 “so green upward doesn’t make sense here.”<\/p>\n<\/blockquote>\n\n\n\n

Add a _HasPriorData<\/code> flag; show a neutral “No prior period to compare” badge
instead of a false increase\/decrease.<\/p>\n\n\n\n

8. Polish: number formatting<\/h2>\n\n\n\n
\n

“Round to millions” (e.g. $8.4M instead of $8,423,838).<\/p>\n<\/blockquote>\n\n\n\n

9. Polish: spacing<\/h2>\n\n\n\n
\n

“Remove spacing in width and height so it uses minimal real estate.”<\/p>\n<\/blockquote>\n\n\n\n

Cuts cell padding, font size, and row height for a denser table.<\/p>\n\n\n\n

10. Polish: scrollbar<\/h2>\n\n\n\n
\n

“Make the scrollbar thin and unobtrusive \u2014 only appears on hover.”<\/p>\n<\/blockquote>\n\n\n\n

Adds a <style><\/code> block with ::-webkit-scrollbar<\/code> rules scoped to a class name
on the scroll container.<\/p>\n\n\n\n

\n\n\n\n

Known pitfalls to mention up front (saves a round-trip)<\/h2>\n\n\n\n
    \n
  • Long single-line string concatenations break in Tabular Editor’s editor<\/strong>
    due to auto-wrap. Fix: break RETURN \"a\" & \"b\" & \"c\"<\/code> into a VAR<\/code> holding
    the built string, then RETURN<\/code> the var \u2014 keeps each line shorter.<\/li>\n\n\n\n
  • MonthPeriod<\/code>-style integer columns<\/strong>: confirm whether they’re a real
    YYYYMM<\/code> sequential key or just a repeating 1\u201312 month index before relying
    on -1<\/code>\/-12<\/code> arithmetic for “prior period.”<\/li>\n\n\n\n
  • Per-point hover tooltips on an SVG sparkline are unreliable<\/strong> in HTML
    Content (sanitizer strips <title><\/code>, z-index\/stacking issues block the
    hover target). A whole-card title=\"...\"<\/code> attribute works fine \u2014 don’t
    chase per-point tooltips unless you have time to spend on it.<\/li>\n\n\n\n
  • DAX string literals need doubled quotes<\/strong> (\"\"<\/code>) for HTML attributes,
    e.g. style=\"\"color:red;\"\"<\/code>.<\/li>\n<\/ul>\n\n\n\n
    \n\n\n\n

    One-shot version (if you already know what you want)<\/h2>\n\n\n\n
    \n

    “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.”<\/p>\n<\/blockquote>\n\n\n\n

    This single prompt reproduces the whole pattern in one pass, skipping the
    incremental back-and-forth \u2014 useful once you’ve done it once and know the
    shape you want.<\/p>\n\n\n\n

    Monthly-grain variant<\/h3>\n\n\n\n

    Use this instead when the comparison should be month-over-month (latest
    month vs. the month before it) rather than year-over-year \u2014 matching the
    “left column” cards from the executive summary page.<\/p>\n\n\n\n

    \n

    “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.”<\/p>\n<\/blockquote>\n\n\n\n

    The only structural difference from the yearly version: anchor on
    DimDate[MonthPeriod]<\/code> (or your model’s month-key column) for both the
    headline and the comparison, using _LatestPeriod<\/code> \/ _LatestPeriod - 1<\/code>
    instead of DimDate[Year]<\/code> \/ _SelectedYear - 1<\/code>. The background sparkline
    and table stay the same either way since they were already monthly.<\/p>\n","protected":false},"excerpt":{"rendered":"

    Prompt Playbook: KPI Card with Background Trend + Scrollable Table (Power BI HTML Content visual, built via DAX) Use this […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[20,11],"tags":[],"class_list":["post-25","post","type-post","status-publish","format-standard","hentry","category-claude-ai","category-power-bi-tutorials"],"_links":{"self":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts\/25","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/comments?post=25"}],"version-history":[{"count":1,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts\/25\/revisions"}],"predecessor-version":[{"id":26,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/posts\/25\/revisions\/26"}],"wp:attachment":[{"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/media?parent=25"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/categories?post=25"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.datascientist.ca\/blog\/wp-json\/wp\/v2\/tags?post=25"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}