Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
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.
Solved! Go to 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.
Thanks Sahir,
This solution was super helpful, ISINSCOPE was the missing piece
Thanks Sahir,
This solution was super helpful, ISINSCOPE was the missing piece
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.