External reference (significance)

The significance tests of the charts compare what the chart shows: a point with the previous category, with a reference category or with a reference series. Very often, though, the reference is something the chart does not show — last year, a benchmark, the total of another scope. Loading that data into the visual only to hide it would go against the way the SDM charts work: they receive figures already computed by the model and draw them.

The External reference test mode answers that need. You drop in the Significance reference data role a measure that returns, for each point of the chart, the reference that point must be compared with. The visual reads it and decorates the point with the display settings of the test.

Available in the Column Chart, the Line Chart, the Radar and the Pie Chart.

How the reference is paired with the point

Power BI evaluates the reference measure in the same filter context as the point it goes with: same category, same legend value. A measure computing last year's score therefore returns, for every point, that point's last-year score — each brand against its own N-1, each quarter against its own quarter one year earlier. There is nothing to pair by hand, and the N-1 rows do not have to be in the visual: the report can be filtered on the current year.

What the measure returns

A short JSON text. Four formats are accepted, from "compute everything" to "just display my result":

JSON	What the visual does	Needs on the displayed point
{"val": 0.42, "n": 2574}	Proportion test between the point and val, with bases n₁ (the point's Base) and n	Base
{"val": 3.87, "n": 2574, "stddev": 0.33}	Mean test between the point and val	Base and Standard Deviation
{"ttest": 2.31}	Compares the statistic you computed with the level of the card: 1.645 / 1.96 / 2.58 at 90 / 95 / 99 %	nothing
{"signif": -1}	Displays your result as is: -1 lower, 0 not significant, 1 higher	nothing

Sign convention

For ttest and signif, a positive value means the displayed point is higher than the reference: compute ttest = (displayed − reference) / se, not the other way round. With the opposite convention every decoration comes out reversed — without any error message.

{"signif"} is not limited to statistics: any rule computed in the model (a business threshold, an in-house test) can use the icons, texts and colours of the chart.

Format detection

The format is read on the first readable value of each series and applies to the whole series. A value in another format in the same series is counted as invalid and left undecorated. A blank measure means "no test on this point" — it is not an error. Likewise, a point whose displayed value is blank is never tested, whatever the format — even a given ttest or signif.

Writing tolerance

The visual accepts what DAX string building usually produces:

• keys in any case, with aliases: val / value, n / base, stddev / sd, ttest / t, signif / s;
• single quotes, unquoted keys, ; as a separator;
• numbers written as strings, with a decimal point or a decimal comma: {"val": "0,42"}.

The only thing that cannot be read is a decimal comma outside quotes — {"val": 0,42} cannot be told apart from a separator. Build numbers with FORMAT(x, "0.##########", "en-US"), which always writes a decimal point whatever the locale of the report, or put them in quotes.

DAX examples

Proportion vs. last year — the visual runs the test:

Signif ref N-1 =
VAR v = CALCULATE ( [Satisfaction %], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR n = CALCULATE ( [Base], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    IF (
        NOT ISBLANK ( v ) && n > 0,
        "{""val"":" & FORMAT ( v, "0.##########", "en-US" )
            & ",""n"":" & FORMAT ( n, "0", "en-US" ) & "}"
    )

Mean vs. a benchmark scope — add the standard deviation:

Signif ref benchmark =
VAR scope = REMOVEFILTERS ( Brand )
VAR m = CALCULATE ( [Average score], scope )
VAR n = CALCULATE ( [Base], scope )
VAR sd = CALCULATE ( [Std dev score], scope )
RETURN
    "{""val"":" & FORMAT ( m, "0.##########", "en-US" )
        & ",""n"":" & FORMAT ( n, "0", "en-US" )
        & ",""stddev"":" & FORMAT ( sd, "0.##########", "en-US" ) & "}"

Statistic or result computed in the model:

Signif ref t = "{""ttest"":" & FORMAT ( [My t statistic], "0.####", "en-US" ) & "}"

Signif ref rule =
"{""signif"":" & SWITCH ( TRUE (), [Delta] > 0.05, 1, [Delta] < -0.05, -1, 0 ) & "}"

One measure, several visuals

The string building is the tedious part. If your Power BI version supports DAX user-defined functions, write it once — for example SignifRef ( value, base, stddev ) — and call it from each reference measure.

Setting up a test

1. Drop the reference measure(s) in Significance reference — up to three, one per test if needed.
2. In the Significance card, set Test N to External reference.
3. In the Test N group:

Property	Description	Default
Reference measure (regex)	Which measure of the Significance reference well this test uses, by its name	(empty — the first one)
Apply to measure (regex)	Which displayed measure(s) the test decorates, by name	(empty — every measure)

Apply to measure matters when several measures are in Values: the reference measure is evaluated per category and per legend value, but it cannot know which displayed measure it is being compared with. Give each test its own target — test 1 on Gross Margin with an N-1 reference, test 2 on Net Margin with another one.

Both fields are case-insensitive regular expressions matched anywhere in the name (N-1, ^Score$, Gross|Net).

Status

The status line of the test reports what was read, with symbols and JSON keys only:

Status	Meaning
✓ "Score N-1" {val, n} · 24 points	Measure found, format detected, 24 points compared
… · 3 not testable	Readable values that could not be tested — base, value or standard deviation missing on the displayed point
… · ⚠ 2 invalid	Values that could not be read, or in another format than the series' one
✗ no measure in the reference well	The Significance reference role is empty
✗ no reference measure matches	Reference measure matches no measure of the well
✗ "…" — no displayed measure matches	Apply to measure matches no displayed measure
✗ "…" — no readable value	The measure returned nothing that parses
⚠ invalid regex	One of the two patterns does not compile

In the Line Chart, the Show calculation (debug) toggle also lists every external comparison, with its operands or its given statistic.

Pie chart

The pie tests shares (slice / series total). With {"val", "n"}, val must therefore be a share on 0..1 as well. {"val", "n", "stddev"} is compared with the raw slice value, as the Means test does.