The ADDMISSINGITEMS function adds to a calculated table created by the SUMMARIZECOLUMNS function the rows that have not been included because they represent null values.
ADDMISSINGITEMS(
<showAllColumn>[, <showAllColumn>]…,
<table>,
<groupingColumn>[, <groupingColumn>]…
[, filterTable]…
)
- showAllColumn: Column from which to extract the elements without data.
- table: Table generated by the SUMMARIZECOLUMNS function that contains the elements with data.
- groupingColumn: Column to use to group the resulting elements.
- filterTable: Table representing filters to determine if an element (or combination of them) should be included or not. It is used to prevent the ADDMISSINGITEMS function from mistakenly adding values that are not present because they have been removed by a filter.
The ADDMISSINGITEMS function returns a table.
Consider the following table containing the list of offices (Oficinas):
...and the following sales table (Ventas):
As we can see, there is an office (Buenos Aires) without sales. If we create a table adding sales by office using the SUMMARIZECOLUMNS function, the result is the following:
Ventas por oficina =
SUMMARIZECOLUMNS(
Oficinas[Localización],
"Ventas", SUM(Ventas[Cantidad])
)
As might be expected, the Buenos Aires office has not been included in the table. If we wanted all the offices to be included regardless of their sales figure, we could use the ADDMISSINGITEMS function:
Ventas por todas oficinas =
ADDMISSINGITEMS(
Oficinas[Localización],
SUMMARIZECOLUMNS(
Oficinas[Localización],
"Ventas", SUM(Ventas[Cantidad])
),
Oficinas[Localización]
)
The first of the three included arguments (Oficinas[Localización]), is the column from which to extract the names of the missing offices. The next argument is the SUMMARIZECOLUMNS function that is generating the aggregate table. Finally, the column or columns to be used to group the result with subtotals are indicated. The result obtained is as shown below:
