Forum Discussion

BOkungbure's avatar
BOkungbure
Regular Visitor
2 years ago
Solved

Matrix Calculation on 1st and 2nd Level

Hello Everyone,

 

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

Main SectionSub SectionValue
Section ASub Section 110
Section ASub Section 29
Section ASub Section 321
Section BSub Section 122
Section BSub Section 275

 

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's avatar
    Sahir_Maharaj
    2 years ago

    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's avatar
    BOkungbure
    2 years ago

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

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    BOkungbure
    Regular Visitor
    2 years ago

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