LOOKUPWITHTOTALS DAX Function (Filter)
Identifies cells where specified columns match evaluated expressions, and retrieves a column value (only when a single cell is identified) or calculates an expression’s value from those matching cells. LOOKUPWITHTOTALS defaults the non-specified columns to the total, whereas LOOKUP does not change the filter over non-specified columns.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Result_ColumnOrExpression |
Specifies the column to retrieve a value from or an expression to evaluate over the identified cells where search_columns match search_values. |
|
| Search_Column | Repeatable |
The column that contains search_value. |
| Search_Value | Repeatable |
The value that you want to find in search_column. |
Return values
The value of the column found, or the result of the expression evaluated in the matching cells.
Remarks
LOOKUPWITHTOTALS can be expressed by using LOOKUP and COLLAPSEALL. COLLAPSEALL moves the evaluation to the total of one axis, then LOOKUP applies the filters. You must use COLLAPSEALL for each axis of the visual. When the visual has only the ROWS axis, the two following formulas are equivalent:
LOOKUP 2018 Totals = COLLAPSEALL ( LOOKUP ( [Sales Amount], [Year], 2018 ), ROWS )
LOOKUP 2018 Totals = LOOKUPWITHTOTALS ( [Sales Amount], [Year], 2018 )
When the visual also has the COLUMNS axis, use COLLAPSEALL for both axes:
LOOKUP 2018 Totals = COLLAPSEALL ( COLLAPSEALL ( LOOKUP ( [Sales Amount], [Year], 2018 ), ROWS ), COLUMNS )
COLLAPSE is different, because it removes only one level of the axis. When the axis has more than two levels, COLLAPSE can return a different result.
Examples
The following query compares LOOKUPWITHTOTALS with COLLAPSEALL in a visual with Year on rows and Category on columns.
-- LOOKUPWITHTOTALS uses the total of every axis.
-- With a COLUMNS axis, COLLAPSEALL on ROWS only is not enough:
-- you need COLLAPSEALL on both ROWS and COLUMNS
DEFINE
COLUMN '__V'[LWT] = LOOKUPWITHTOTALS ( [Sales Amount], [Year], 2008 )
COLUMN '__V'[CollapseAllRows] = COLLAPSEALL ( LOOKUP ( [Sales Amount], [Year], 2008 ), ROWS )
COLUMN '__V'[CollapseAllBoth] = COLLAPSEALL ( COLLAPSEALL ( LOOKUP ( [Sales Amount], [Year], 2008 ), ROWS ), COLUMNS )
VAR __Core =
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL ( 'Date'[Calendar Year Number], "IsGrandTotalRowTotal" ),
ROLLUPADDISSUBTOTAL ( 'Product'[Category], "IsGrandTotalColumnTotal" ),
TREATAS ( { "Audio", "Computers" }, 'Product'[Category] ),
"Sales_Amount", [Sales Amount]
)
VAR __Input =
SELECTCOLUMNS (
__Core,
"Year", 'Date'[Calendar Year Number],
"Category", 'Product'[Category],
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"IsGrandTotalColumnTotal", [IsGrandTotalColumnTotal],
"Sales Amount", [Sales_Amount]
)
TABLE '__V' = __Input
WITH VISUAL SHAPE
AXIS ROWS
GROUP [Year] TOTAL [IsGrandTotalRowTotal]
ORDER BY [Year] ASC
AXIS COLUMNS
GROUP [Category] TOTAL [IsGrandTotalColumnTotal]
ORDER BY [Category] ASC
DENSIFY "IsDensifiedRow"
EVALUATE
SELECTCOLUMNS ( '__V', "Year", [Year], "Category", [Category], "Sales Amount", [Sales Amount], "LOOKUPWITHTOTALS", [LWT], "COLLAPSEALL ROWS", [CollapseAllRows], "COLLAPSEALL ROWS COLUMNS", [CollapseAllBoth] )
ORDER BY [Year], [Category]
| Year | Category | Sales Amount | LOOKUPWITHTOTALS | COLLAPSEALL ROWS | COLLAPSEALL ROWS COLUMNS |
|---|---|---|---|---|---|
| (Blank) | (Blank) | 7,126,066.89 | 2,171,705.17 | 2,171,705.17 | 2,171,705.17 |
| (Blank) | Audio | 384,518.16 | 2,171,705.17 | 105,363.42 | 2,171,705.17 |
| (Blank) | Computers | 6,741,548.73 | 2,171,705.17 | 2,066,341.75 | 2,171,705.17 |
| 2007 | (Blank) | 2,763,040.93 | 2,171,705.17 | 2,171,705.17 | 2,171,705.17 |
| 2007 | Audio | 102,722.07 | 2,171,705.17 | 105,363.42 | 2,171,705.17 |
| 2007 | Computers | 2,660,318.87 | 2,171,705.17 | 2,066,341.75 | 2,171,705.17 |
| 2008 | (Blank) | 2,171,705.17 | 2,171,705.17 | 2,171,705.17 | 2,171,705.17 |
| 2008 | Audio | 105,363.42 | 2,171,705.17 | 105,363.42 | 2,171,705.17 |
| 2008 | Computers | 2,066,341.75 | 2,171,705.17 | 2,066,341.75 | 2,171,705.17 |
| 2009 | (Blank) | 2,191,320.78 | 2,171,705.17 | 2,171,705.17 | 2,171,705.17 |
| 2009 | Audio | 176,432.67 | 2,171,705.17 | 105,363.42 | 2,171,705.17 |
| 2009 | Computers | 2,014,888.11 | 2,171,705.17 | 2,066,341.75 | 2,171,705.17 |
Related functions
Other related functions are:
Last update: Oct 24, 2026 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
Microsoft documentation: https://learn.microsoft.com/en-us/dax/lookupwithtotals-function-dax