topic Re: Matrix Calculation on 1st and 2nd Level in DAX Commands and Tips

Matrix Calculation on 1st and 2nd Level

BOkungbure — Wed, 04 Oct 2023 14:30:47 GMT

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.

Re: Matrix Calculation on 1st and 2nd Level

Sahir_Maharaj — Fri, 06 Oct 2023 16:45:34 GMT

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.

Re: Matrix Calculation on 1st and 2nd Level

BOkungbure — Sat, 28 Oct 2023 19:41:15 GMT

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