Daily DAX : Day 289 ROLLUPISSUBTOTAL

 The **ROLLUPISSUBTOTAL** function in Power BI DAX is a specialized function used in conjunction with the **ROLLUPGROUP** function to identify whether a specific column in a calculation is being subtotaled or totaled in a summarized table, such as in a matrix or pivot table. It helps in scenarios where you need to apply different logic for subtotals or grand totals compared to regular row-level calculations.

### Syntax

```dax

ROLLUPISSUBTOTAL ( <ColumnName> )

```

- **ColumnName**: The name of the column to check if it is being subtotaled or totaled.

### Return Value

- Returns **TRUE** if the specified column is being subtotaled or totaled in the current calculation context.

- Returns **FALSE** if the specified column is not being subtotaled (i.e., it’s part of a regular row-level calculation).

### How It Works

The **ROLLUPISSUBTOTAL** function is typically used within a **CALCULATE** or **SUMMARIZE** expression alongside **ROLLUPGROUP** to modify behavior for subtotals or grand totals in a report. It’s particularly useful in complex matrix visuals or tables where you want to customize how subtotals are calculated or displayed.

### Use Case

**Scenario**: You have a sales dataset with columns for **Region**, **Category**, and **SalesAmount**. In a matrix visual, you want to display the **SalesAmount** for individual rows but apply a different calculation (e.g., a percentage of total sales) for subtotals or grand totals.

#### Example

Suppose you want to show:

- Regular row-level sales amounts for each **Region** and **Category**.

- For subtotals (e.g., total per Region or grand total), display the percentage of total sales instead of the raw sum.

Here’s how you can use **ROLLUPISSUBTOTAL**:

```dax

CustomSales = 

VAR TotalSales = SUM(Sales[SalesAmount])

VAR GrandTotalSales = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales))

RETURN

SUMMARIZE(

    ROLLUPGROUP(Sales[Region], Sales[Category]),

    Sales[Region],

    Sales[Category],

    "SalesMeasure",

    IF(

        ROLLUPISSUBTOTAL(Sales[Region]) || ROLLUPISSUBTOTAL(Sales[Category]),

        DIVIDE(TotalSales, GrandTotalSales, 0), -- Show percentage for subtotals/totals

        TotalSales -- Show regular sales for row-level data

    )

)

```

### Explanation of the Example

1. **ROLLUPGROUP(Sales[Region], Sales[Category])**: This creates a summarized table that includes subtotals and grand totals for **Region** and **Category**.

2. **ROLLUPISSUBTOTAL(Sales[Region]) || ROLLUPISSUBTOTAL(Sales[Category])**: Checks if the current row in the summarized table is a subtotal or grand total for either **Region** or **Category**.

3. **IF Condition**:

   - If **TRUE** (i.e., it’s a subtotal or grand total), calculate the percentage of total sales (`DIVIDE(TotalSales, GrandTotalSales, 0)`).

   - If **FALSE** (i.e., it’s a regular row), return the raw sales amount (`TotalSales`).

4. The result is a matrix where regular rows show sales amounts, and subtotals/grand totals show percentages.

### Practical Use Cases

1. **Custom Subtotal Logic**: Use **ROLLUPISSUBTOTAL** to apply different calculations for subtotals, such as averages, percentages, or custom metrics, instead of the default sum.

2. **Conditional Formatting**: Highlight or format subtotals differently in a matrix or table visual.

3. **Complex Aggregations**: When working with hierarchical data (e.g., Region > Category > Product), customize how subtotals are computed at each level.

4. **Financial Reporting**: Display raw values for individual rows but show ratios, percentages, or weighted averages for subtotals or grand totals.

### Key Notes

- **ROLLUPISSUBTOTAL** is only meaningful when used with **ROLLUPGROUP** or **ROLLUP** in a **SUMMARIZE** or **ADDCOLUMNS** context.

- It’s designed for advanced DAX scenarios involving matrix or pivot table visuals where subtotals need special handling.

- Ensure the column referenced in **ROLLUPISSUBTOTAL** is part of the **ROLLUPGROUP** definition; otherwise, it will always return **FALSE**.

### Limitations

- The function is context-sensitive and requires a clear understanding of the calculation context in DAX.

- It’s not commonly used in simple DAX calculations and is more relevant for advanced reporting needs.