Row Subtotals, Incorrect Sum in Matrix

Hi Syndicate_Admin ,

Thanks for reaching out to Microsoft Fabric Community.

In Matrix visuals, when using DISTINCTCOUNT, the subtotal rows (such as total customers per week across years) may not behave as expected. By default, Power BI calculates the distinct count at the subtotal level across all underlying data, rather than summing the individual distinct counts shown in each row or column.

To correct this and ensure that the subtotals reflect the sum of distinct counts per week and per year, you can use a measure with ISINSCOPE() to handle each level of the matrix layout appropriately.

I tested this behavior with the following sample data:

Measure:

SalesPerson Count (Correct Totals) = 
VAR IsYearInScope = ISINSCOPE(SalesData[Year])
VAR IsWeekInScope = ISINSCOPE(SalesData[Week])

-- 1. Cell-level (Year & Week in scope)
VAR CellValue =
    IF(
        IsYearInScope && IsWeekInScope,
        CALCULATE(DISTINCTCOUNT(SalesData[SalesPerson]))
    )

-- 2. Row Subtotal (Year in scope only — across weeks)
VAR YearTotal =
    IF(
        IsYearInScope && NOT IsWeekInScope,
        SUMX(
            VALUES(SalesData[Week]),
            CALCULATE(DISTINCTCOUNT(SalesData[SalesPerson]))
        )
    )

-- 3. Column Subtotal (Week in scope only — across years)
VAR WeekTotal =
    IF(
        NOT IsYearInScope && IsWeekInScope,
        SUMX(
            VALUES(SalesData[Year]),
            CALCULATE(DISTINCTCOUNT(SalesData[SalesPerson]))
        )
    )

-- 4. Grand Total (neither in scope)
VAR GrandTotal =
    IF(
        NOT IsYearInScope && NOT IsWeekInScope,
        SUMX(
            VALUES(SalesData[Year]),
            SUMX(
                VALUES(SalesData[Week]),
                CALCULATE(DISTINCTCOUNT(SalesData[SalesPerson]))
            )
        )
    )

RETURN
    COALESCE(CellValue, YearTotal, WeekTotal, GrandTotal)

Output:

This approach ensures that subtotals and grand totals represent the sum of the individual weekly or yearly distinct counts, instead of a distinct count across the entire subtotal group.

Hope this helps. 

If this doesn't fully address your scenario, please share a sample dataset or .pbix file (with any sensitive information removed), so we can provide more targeted assistance.

If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.

Attaching .pbix file for reference.