ISSUBTOTAL
In SUMMARIZE with ROLLUP, whether the row is a subtotal of a column.
Group: Table manipulation. Compatible with DAX.
Syntax
ISSUBTOTAL ( <columnName> )Returns
Boolean. TRUE on a subtotal row of that column, FALSE on the other rows.
Remarks
- It belongs in the name and expression pairs of a SUMMARIZE that uses ROLLUP. On a subtotal row the rolled-up column is blank; ISSUBTOTAL tells such a row from one whose value really is blank.
- On the grand total row every rolled-up column is a subtotal, so ISSUBTOTAL is TRUE for each.
- In
SUMMARIZECOLUMNS,ROLLUPADDISSUBTOTALmakes the TRUE/FALSE column itself, and ISSUBTOTAL is not needed.
Examples
A grand total row
EVALUATE
SUMMARIZE (
Sales,
ROLLUP ( Products[Category] ),
"Liters", SUM ( Sales[Liters] ),
"Total row", ISSUBTOTAL ( Products[Category] )
)
ORDER BY [Total row], Products[Category]| Products[Category] | Liters | Total row |
|---|---|---|
| Beer | 1505389 | FALSE |
| Cider | 301074 | FALSE |
| (blank) | 1806463 | TRUE |
Naming the level of each row
EVALUATE
SUMMARIZE (
Sales,
ROLLUP ( Products[Category], Products[Product] ),
"Liters", SUM ( Sales[Liters] ),
"Level",
IF (
ISSUBTOTAL ( Products[Category] ), "Grand total",
IF ( ISSUBTOTAL ( Products[Product] ), "Category", "Product" )
)
)
ORDER BY Products[Category], Products[Product]| Products[Category] | Products[Product] | Liters | Level |
|---|---|---|---|
| (blank) | (blank) | 1806463 | Grand total |
| Beer | (blank) | 1505389 | Category |
| Beer | IPA | 331884 | Product |
| Beer | Lettøl | 169179 | Product |
| Beer | Pils | 1004326 | Product |
| Cider | (blank) | 301074 | Category |
| Cider | Cider | 301074 | Product |