LOOKUPWITHTOTALS

As LOOKUP, with the columns not named taken at their totals.

A visual calculation works on the table behind a visual: a row for each group on the visual's axes and one for each of their totals. To try one in the OAX view, give a table that shape with DEFINE TABLE ... WITH VISUAL SHAPE: its axes, their groups, the TRUE/FALSE column that marks each group's totals and the order, and with DENSIFY a name for the column that marks rows the shape fills in. Then write the calculation as a COLUMN of that table, as Power BI's own queries do. The examples below are queries of that kind, small enough to check by hand.

Group: Window and visual calculations. Compatible with DAX.

Syntax

LOOKUPWITHTOTALS ( <expression>, <column>, <value> [, <column>, <value>] … )

Returns

Same type as the expression. The expression's value at the row the values name, every other field at its total.

Remarks

• Unlike LOOKUP, nothing is taken from the current row, so the answer is the same on every row of the visual.
• LOOKUPWITHTOTALS ( [Liters], [Year], 2025 ) is the 2025 total over all products, wherever it is asked.
• With years and quarters on one axis, a quarter alone matches a row in each year, which is an error: name the year too, LOOKUPWITHTOTALS ( [Liters], [Year], 2026, [Quarter], 1 ).
• When no row has the values, the result is BLANK.
• It works only in a visual calculation; in a measure, a calculated column or a plain query it is an error.

Examples

With the current row, and with totals

DEFINE
    TABLE Visual =
        DATATABLE (
            "Product", STRING, "Year", INTEGER, "Liters", INTEGER, "All products", BOOLEAN, "All years", BOOLEAN,
            {
                { "IPA", 2025, 60, FALSE, FALSE }, { "IPA", 2026, 80, FALSE, FALSE }, { "IPA", BLANK (), 140, FALSE, TRUE },
                { "Pils", 2025, 100, FALSE, FALSE }, { "Pils", 2026, 120, FALSE, FALSE }, { "Pils", BLANK (), 220, FALSE, TRUE },
                { BLANK (), 2025, 160, TRUE, FALSE }, { BLANK (), 2026, 200, TRUE, FALSE }, { BLANK (), BLANK (), 360, TRUE, TRUE }
            }
        )
        WITH VISUAL SHAPE
            AXIS ROWS GROUP [Product] TOTAL [All products] ORDER BY [Product]
            AXIS COLUMNS GROUP [Year] TOTAL [All years] ORDER BY [Year]
            DENSIFY "Added"
    COLUMN Visual[LOOKUP] = LOOKUP ( [Liters], [Year], 2025 )
    COLUMN Visual[LOOKUPWITHTOTALS] = LOOKUPWITHTOTALS ( [Liters], [Year], 2025 )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Product", [Product], "Year", [Year], "Liters", [Liters],
    "LOOKUP", [LOOKUP],
    "LOOKUPWITHTOTALS", [LOOKUPWITHTOTALS]
)
ORDER BY ISBLANK ( [Product] ), [Product], ISBLANK ( [Year] ), [Year]

One product, or one cell

Pils over both years, and Pils in 2026: the same two numbers on every row.

DEFINE
    TABLE Visual =
        DATATABLE (
            "Product", STRING, "Year", INTEGER, "Liters", INTEGER, "All products", BOOLEAN, "All years", BOOLEAN,
            {
                { "IPA", 2025, 60, FALSE, FALSE }, { "IPA", 2026, 80, FALSE, FALSE }, { "IPA", BLANK (), 140, FALSE, TRUE },
                { "Pils", 2025, 100, FALSE, FALSE }, { "Pils", 2026, 120, FALSE, FALSE }, { "Pils", BLANK (), 220, FALSE, TRUE },
                { BLANK (), 2025, 160, TRUE, FALSE }, { BLANK (), 2026, 200, TRUE, FALSE }, { BLANK (), BLANK (), 360, TRUE, TRUE }
            }
        )
        WITH VISUAL SHAPE
            AXIS ROWS GROUP [Product] TOTAL [All products] ORDER BY [Product]
            AXIS COLUMNS GROUP [Year] TOTAL [All years] ORDER BY [Year]
            DENSIFY "Added"
    COLUMN Visual[Pils total] = LOOKUPWITHTOTALS ( [Liters], [Product], "Pils" )
    COLUMN Visual[Pils in 2026] = LOOKUPWITHTOTALS ( [Liters], [Product], "Pils", [Year], 2026 )
EVALUATE
SELECTCOLUMNS (
    Visual,
    "Product", [Product], "Year", [Year], "Liters", [Liters],
    "Pils total", [Pils total],
    "Pils in 2026", [Pils in 2026]
)
ORDER BY ISBLANK ( [Product] ), [Product], ISBLANK ( [Year] ), [Year]

Related functions

LOOKUP, COLLAPSEALL, DIVIDE.

Last updated 3 November 2026 · OpenBI Studio 0.3.74