Incorrect Sub Totals for Matrix Visual

[Anonymous]

Hi everyone,

I am facing an issue with wrong sub totals in a Matrix Visual.

I have a hierarchy containing 3+ different categories. For example, National Level Manager, Region Level Mananger, State Level Manager, etc. I have one measure, which basically computes a divisionn Operation( Sum of Value/Count of Certain Items). Lets say this measure as Index 1. I need to find out another Measure Index2, which is basically (Index1/SubTotal Index1 Value). This measure is dynamic. It should be at the lowest level of open hierarchy in the matrix. Sharing below the screen shots.

For example: Region Level Manager- jitendra in first screenshot, index 2 is 289.33/303.85=0.95. Similarly in second screenshot, for State level Manager-Ankit Mishra, Index2 is 270.78/289.33=0.94. All of these values are correct. The only wrong values are subtotal in both cases. Ideally total value should be 1. (303.85/303.85 and 289.33/289.33)

Index 1 =

DIVIDE(

    SUM('Test Data'[Value]),

    DISTINCTCOUNT('Test Data'[String Code])

)

Index 2 =

IF(

    ISINSCOPE('Test Data'[State Level Manager]),

    DIVIDE(

        CALCULATE([Index 1]),

        CALCULATE(

            [Index 1],

            ALL('Test Data'[State Level Manager])

        )

    ),

    IF(

        ISINSCOPE('Test Data'[Region Level Manager]),

        DIVIDE(

            CALCULATE([Index 1]),

            CALCULATE(

                [Index 1],

                All('Test Data'[Region Level Manager])

            )

        ),

        IF(

            ISINSCOPE('Test Data'[National Level Manager]),

            DIVIDE(

                CALCULATE([Index 1]),

                CALCULATE(

                    [Index 1],

                    All('Test Data'[National Level Manager])

                )

            ),

            1

        )

    )

)

Please note: The data shown in screenshot is fictious and is only for representation purpose.

Any help would be highly appreciated. Thank you.