Calculation Groups, Format Strings, Parameters

What it is

Three model features reduce the number of measures and make reports more flexible.

• A calculation group applies the same logic, such as year to date or prior year, to any measure.
• A dynamic format string changes how a value is displayed, such as currency symbol or K and M suffixes, while the value stays a number.
• A field parameter lets report users choose which measure or column a visual shows.

How it works

Calculation groups

A calculation group is a special table with one column. Each row is a calculation item, a DAX expression that uses SELECTEDMEASURE() to stand for whatever measure the visual is showing.

-- Calculation group: Time Intelligence
Current = SELECTEDMEASURE ()

YTD = CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date] ) )

PY = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

YoY % =
VAR CurrentValue = SELECTEDMEASURE ()
VAR PriorValue =
    CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    DIVIDE ( CurrentValue - PriorValue, PriorValue )

With three base measures (Sales, Cost, Margin) and four items, users get twelve results while you maintain only seven definitions.

You create calculation groups in Power BI Desktop's Model view or with an external tool such as Tabular Editor. Key details:

Dynamic format strings for measures

Set a measure's format to Dynamic and write a DAX expression that returns a format string. The value stays numeric, so it still sorts, totals and plots correctly.

-- Format string expression for the measure [Sales in Currency]
SELECTEDVALUE (
    'Currency'[Format String],
    "\$#,0.00;(\$#,0.00);\$#,0.00"
)

The same idea works in a calculation item's format string expression, where SELECTEDMEASURE() gives the value being formatted:

SWITCH (
    TRUE (),
    SELECTEDMEASURE () >= 1000000, "#,0,,.0\M",
    SELECTEDMEASURE () >= 1000, "#,0,.0\K",
    "#,0"
)

The older way was the FORMAT function, which returns text. A text result cannot be plotted on a chart axis and sorts alphabetically, which is why dynamic format strings are the preferred answer.

Field parameters

You create one with Modeling > New parameter > Fields and pick measures or columns. Power BI builds a calculated table that uses NAMEOF, and can add a slicer for it:

Metric = {
    ( "Sales", NAMEOF ( 'Sales'[Total Sales] ), 0 ),
    ( "Margin", NAMEOF ( 'Sales'[Total Margin] ), 1 ),
    ( "Orders", NAMEOF ( 'Sales'[Order Count] ), 2 )
}

Put the parameter on a visual's axis or values and a slicer on the page. When users pick "Margin" in the slicer, the visual shows the Margin measure instead of Sales. A field parameter of columns works the same way for dimensions, such as switching the axis between Region, Product and Channel.

Which feature to use

A worked example

A finance model has Revenue, Cost and Margin, reported in several currencies. Managers want each with YTD and prior year, and one chart where they choose the measure.

1. Create a Time Intelligence calculation group with Current, YTD, PY and YoY % items; give YoY % the format string "0.0%".
2. Give the converted revenue measure a dynamic format string that reads the selected currency's format from a Currency table.
3. Create a field parameter with Revenue, Cost and Margin, put it on the chart's values, and add its slicer to the page.

How the exam asks about this

• "Avoid creating YTD and PY versions of 30 measures." A calculation group with SELECTEDMEASURE().
• "Show values in the currency the user selects while keeping them numeric." A dynamic format string.
• "Users must choose whether a chart shows sales, margin or units." A field parameter.