Reply
Stuznet
Helper V
Helper V

Matrix Row Subtotal Incorrect

Hi guys,

I'm having trouble with the Matrix Row Subtotal summing incorrectly. Can you help? 

Here is the dummy report.

 

Capture.PNG

Thanks

1 ACCEPTED SOLUTION

Hi @Stuznet ,

 

I am so sorry that I missed it, now I have fixed it, try to use following measure.

 

Measure =
VAR r3total =
    SUMX (
        ADDCOLUMNS (
            'Month Sort',
            "1", CALCULATE ( [Row1] ),
            "2", CALCULATE ( [Row2] )
        ),
        ( [1] + [2] ) * ( 13 - [Month Num] )
    )
RETURN
    IF (
        ISINSCOPE ( 'ROW'[Category] ),
        SWITCH (
            TRUE (),
            MAX ( 'ROW'[Category] ) = "ROW1", IF (
                ISINSCOPE ( 'Month Sort'[Month] ),
                CALCULATE ( [Row1] ),
                CALCULATE (
                    [Row1],
                    FILTER ( 'Data', Data[Month Num] + 1 >= MONTH ( TODAY () ) )
                )
            ),
            MAX ( 'ROW'[Category] ) = "ROW2", IF (
                ISINSCOPE ( 'Month Sort'[Month] ),
                CALCULATE ( [Row2] ),
                CALCULATE (
                    [Row2],
                    FILTER ( 'Data', Data[Month Num] + 2 <= MONTH ( TODAY () ) )
                )
            ),
            MAX ( 'ROW'[Category] ) = "ROW3", IF ( ISINSCOPE ( 'Month Sort'[Month] ), CALCULATE ( [Row3] ), r3total )
        ),
        CALCULATE ( [Row1] ) + CALCULATE ( [Row2] ) + r3total
    )

1.PNG

 

BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-lid-msft
Community Support
Community Support

Hi @Stuznet ,

 

This is caused by the max funcution in your measure while the total means all the month. You can change the measure to the following.

 

Measure = 
IF (
    ISINSCOPE ( 'ROW'[Category] ),
    SWITCH (
        TRUE (),
        MAX ( 'ROW'[Category] ) = "ROW1", IF (
            ISINSCOPE ( 'Month Sort'[Month] ),
            CALCULATE ( [Row1] ),
            CALCULATE (
                [Row1],
                FILTER ( 'Data', Data[Month Num] + 1 >= MONTH ( TODAY () ) )
            )
        ),
        MAX ( 'ROW'[Category] ) = "ROW2", IF (
            ISINSCOPE ( 'Month Sort'[Month] ),
            CALCULATE ( [Row2] ),
            CALCULATE (
                [Row2],
                FILTER ( 'Data', Data[Month Num] + 2 <= MONTH ( TODAY () ) )
            )
        ),
        MAX ( 'ROW'[Category] ) = "ROW3", IF (
            ISINSCOPE ( 'Month Sort'[Month] ),
            CALCULATE ( [Row3] ),
            CALCULATE (
                [Row1],
                FILTER ( 'Data', Data[Month Num] + 1 >= MONTH ( TODAY () ) )
            )
                + CALCULATE (
                    [Row2],
                    FILTER ( 'Data', Data[Month Num] + 2 <= MONTH ( TODAY () ) )
                )
        )
    ),
    CALCULATE ( [Row1] ) + CALCULATE ( [Row2] )
        + CALCULATE ( [Row3] )
)

We use the inscope to judge the total and calculate different value for different scope.

 

15.PNG

 

If it doesn't meet your requirement, kindly share your expected result to me based on your dummy report.

 

BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you so much! You’re da best! 🙂

Hi @v-lid-msft,

This is a great start but Row 3 total 780 is not correct it should be 3640. Any idea how can I correct it?

Thanks

Hi @Stuznet ,

 

I am so sorry that I missed it, now I have fixed it, try to use following measure.

 

Measure =
VAR r3total =
    SUMX (
        ADDCOLUMNS (
            'Month Sort',
            "1", CALCULATE ( [Row1] ),
            "2", CALCULATE ( [Row2] )
        ),
        ( [1] + [2] ) * ( 13 - [Month Num] )
    )
RETURN
    IF (
        ISINSCOPE ( 'ROW'[Category] ),
        SWITCH (
            TRUE (),
            MAX ( 'ROW'[Category] ) = "ROW1", IF (
                ISINSCOPE ( 'Month Sort'[Month] ),
                CALCULATE ( [Row1] ),
                CALCULATE (
                    [Row1],
                    FILTER ( 'Data', Data[Month Num] + 1 >= MONTH ( TODAY () ) )
                )
            ),
            MAX ( 'ROW'[Category] ) = "ROW2", IF (
                ISINSCOPE ( 'Month Sort'[Month] ),
                CALCULATE ( [Row2] ),
                CALCULATE (
                    [Row2],
                    FILTER ( 'Data', Data[Month Num] + 2 <= MONTH ( TODAY () ) )
                )
            ),
            MAX ( 'ROW'[Category] ) = "ROW3", IF ( ISINSCOPE ( 'Month Sort'[Month] ), CALCULATE ( [Row3] ), r3total )
        ),
        CALCULATE ( [Row1] ) + CALCULATE ( [Row2] ) + r3total
    )

1.PNG

 

BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Nathaniel_C
Community Champion
Community Champion

@Stuznet 

Are you using a measure?

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yes, I'm using a measure. I provided a dummy report, feel free take a look

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)