Row Subtotals, Incorrect Sum in Matrix

[Syndicate_Admin]

I have a matrix in which I must know the total customers per year and per week, what happens is that the total customers is fine per year and week but the sum of the row subtotal is incorrect, how can that problem be corrected. In the values, I'm using distinctive count.

[v-veshwara-msft]

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.

[v-veshwara-msft]

Hi @Syndicate_Admin ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.

[speedramps]

Learn about "x" interative functions like SUMX

https://learn.microsoft.com/en-us/dax/sumx-function-dax

https://www.youtube.com/watch?app=desktop&v=vcYz0XPklXg&t=220s

I want to help you more but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

https://www.youtube.com/watch?app=desktop&v=vcYz0XPklXg&t=220s