Matrix Calculation on 1st and 2nd Level

[BOkungbure]

Hello Everyone,

I have a table that has data arranged into main sections and subsections as shown below.

Main Section	Sub Section	Value
Section A	Sub Section 1	10
Section A	Sub Section 2	9
Section A	Sub Section 3	21
Section B	Sub Section 1	22
Section B	Sub Section 2	75

I want create and measure that will show the ratio of the total value of each main section as well as the ratio of its subsections compared to the highest total across all other sections when expanded in a matrix visual.

So far, my measure only shows the ratio of the main section total compared to the maximum total in that table,

how do I ensure that when I expand to the subsections under their parent sections, the ratio calculation persists for each individual value of the subsections.

[Sahir_Maharaj · Accepted solution]

Hello @BOkungbure,

Can you please try this:

1. Calculate the Maximum Total across all Sections

Max Total Value = 
MAXX(
    ALL('YourTableName'[Main Section]),
    SUMX(
        FILTER(
            'YourTableName',
            'YourTableName'[Main Section] = EARLIER('YourTableName'[Main Section])
        ),
        'YourTableName'[Value]
    )
)

2. Calculate the Ratio

Ratio = 
VAR MainSectionTotal = 
SUMX(
    FILTER(
        'YourTableName',
        'YourTableName'[Main Section] = EARLIER('YourTableName'[Main Section])
    ),
    'YourTableName'[Value]
)

VAR CurrentValue = 
IF(
    ISINSCOPE('YourTableName'[Sub Section]),
    SUM('YourTableName'[Value]),
    MainSectionTotal
)

RETURN
DIVIDE(CurrentValue, [Max Total Value])

Should you require any further assistance, feel free to reach out to me.

[BOkungbure · Accepted solution]

Thanks Sahir,
This solution was super helpful, ISINSCOPE was the missing piece

[Sahir_Maharaj · Accepted solution]

Hello @BOkungbure,

Can you please try this:

1. Calculate the Maximum Total across all Sections

Max Total Value = 
MAXX(
    ALL('YourTableName'[Main Section]),
    SUMX(
        FILTER(
            'YourTableName',
            'YourTableName'[Main Section] = EARLIER('YourTableName'[Main Section])
        ),
        'YourTableName'[Value]
    )
)

2. Calculate the Ratio

Ratio = 
VAR MainSectionTotal = 
SUMX(
    FILTER(
        'YourTableName',
        'YourTableName'[Main Section] = EARLIER('YourTableName'[Main Section])
    ),
    'YourTableName'[Value]
)

VAR CurrentValue = 
IF(
    ISINSCOPE('YourTableName'[Sub Section]),
    SUM('YourTableName'[Value]),
    MainSectionTotal
)

RETURN
DIVIDE(CurrentValue, [Max Total Value])

Should you require any further assistance, feel free to reach out to me.