cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Regular Visitor

## Comparing the Last Level in a Hierarchy with Values from Above Levels

Hey PowerBI Community,

Over the past few days I've been grappling with a problem that's got me stumped.

I'm hoping you can lend a hand.

Table:

My dataset has 7 columns and 4 levels:

Column 1: Year

Column 2: Class Name

Column 3: Class Level

Column 4: School Name

Column 5: Region

Column 6: Number of students per class

Column 7: Average score per class

 Year Class Level School Region Total Students Score 2023 Class A Medior School X Region 1 25 7.5 2023 Class B Senior School Y Region 2 30 8.2 2023 Class C Junior School Z Region 1 20 6.9 2023 Class D Medior School X Region 2 27 7.8 2023 Class E Senior School Y Region 1 32 8.5 2023 Class F Medior School Z Region 3 22 7.0 2023 Class G Senior School X Region 1 28 8.1 2023 Class H Junior School Y Region 2 18 6.7 2023 Class I Medior School Z Region 3 29 7.9 2023 Class J Senior School X Region 2 26 8.3 2024 Class K Medior School Y Region 1 21 7.2 2024 Class L Senior School Z Region 2 31 8.6 2024 Class M Junior School X Region 1 23 7.3 2024 Class N Medior School Y Region 3 33 8.7 2024 Class O Senior School Z Region 1 24 7.4 2024 Class P Junior School X Region 3 34 8.8 2024 Class Q Medior School Y Region 2 19 6.8 2024 Class R Senior School Z Region 3 35 8.9 2024 Class S Junior School X Region 2 36 9.0 2024 Class T Medior School Y Region 1 37 9.1

Visual:

I'd like to create a matrix that shows the following:

Rows: the different levels, such as Region, School Name, Class Level, and Class Name.

Columns: Year

Value: 1 DAX measure

Objective:

With a slicer, I want to be able to see the totals of the levels above when I filter on them. This is to compare the class average with all levels.

Problem:

So far, whenever I select, for example, class T, I consistently see the same value:

Region 1 = 37

School Y = 37

Medior = 37

Class T = 37

But what I expect is:

Region 1 = 210

School Y = 90

Medior= 58

Class T = 37

Your help would be greatly appreciated.

Kind regards,

Sam

1 ACCEPTED SOLUTION
Community Support

Hi @SamChan ,

Depending on the information you have provided, you can follow these steps below:

1.Add new table with out relationship.

Table 2 = DISTINCT('Table'[Class])

Flag =
VAR _selectedtable =
ALLSELECTED ( 'Table 2'[Class] )
VAR _classtable =
CALCULATETABLE ( VALUES ( 'Table'[Class] ), 'Table'[Class] IN _selectedtable )
VAR _leveltable =
CALCULATETABLE ( VALUES ( 'Table'[Level] ), 'Table'[Class] IN _selectedtable )
VAR _schooltable =
CALCULATETABLE ( VALUES ( 'Table'[School] ), 'Table'[Class] IN _selectedtable )
VAR _regiontable =
CALCULATETABLE ( VALUES ( 'Table'[Region] ), 'Table'[Class] IN _selectedtable )
VAR _class =
SELECTEDVALUE ( 'Table'[Class] )
VAR _level =
SELECTEDVALUE ( 'Table'[Level] )
VAR _school =
SELECTEDVALUE ( 'Table'[School] )
VAR _region =
SELECTEDVALUE ( 'Table'[Region] )
RETURN
IF (
_class
IN _classtable
&& ( _level
IN _leveltable
|| _school
IN _schooltable
|| _region IN _regiontable ),
1
)
Result =
VAR __level1 =
ISINSCOPE ( 'Table'[Class] )
VAR __level2 =
ISINSCOPE ( 'Table'[Level] )
VAR __level3 =
ISINSCOPE ( 'Table'[School] )
VAR __level4 =
ISINSCOPE ( 'Table'[Region] )
VAR __level1_code = [TotalStudents]
VAR __level2_code =
CALCULATE ( [TotalStudents], ALL ( 'Table'[Class], 'Table'[Year] ) )
VAR __level3_code =
CALCULATE (
[TotalStudents],
ALL ( 'Table'[Class], 'Table'[Level], 'Table'[Year] )
)
VAR __level4_code =
CALCULATE (
[TotalStudents],
ALL ( 'Table'[Class], 'Table'[Level], 'Table'[School], 'Table'[Year] )
)
VAR __result =
SWITCH (
TRUE (),
__level1, __level1_code,
__level2, __level2_code,
__level3, __level3_code,
__level4, __level4_code
)
RETURN
__result

Final output:

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @SamChan ,

Depending on the information you have provided, you can follow these steps below:

1.Add new table with out relationship.

Table 2 = DISTINCT('Table'[Class])

Flag =
VAR _selectedtable =
ALLSELECTED ( 'Table 2'[Class] )
VAR _classtable =
CALCULATETABLE ( VALUES ( 'Table'[Class] ), 'Table'[Class] IN _selectedtable )
VAR _leveltable =
CALCULATETABLE ( VALUES ( 'Table'[Level] ), 'Table'[Class] IN _selectedtable )
VAR _schooltable =
CALCULATETABLE ( VALUES ( 'Table'[School] ), 'Table'[Class] IN _selectedtable )
VAR _regiontable =
CALCULATETABLE ( VALUES ( 'Table'[Region] ), 'Table'[Class] IN _selectedtable )
VAR _class =
SELECTEDVALUE ( 'Table'[Class] )
VAR _level =
SELECTEDVALUE ( 'Table'[Level] )
VAR _school =
SELECTEDVALUE ( 'Table'[School] )
VAR _region =
SELECTEDVALUE ( 'Table'[Region] )
RETURN
IF (
_class
IN _classtable
&& ( _level
IN _leveltable
|| _school
IN _schooltable
|| _region IN _regiontable ),
1
)
Result =
VAR __level1 =
ISINSCOPE ( 'Table'[Class] )
VAR __level2 =
ISINSCOPE ( 'Table'[Level] )
VAR __level3 =
ISINSCOPE ( 'Table'[School] )
VAR __level4 =
ISINSCOPE ( 'Table'[Region] )
VAR __level1_code = [TotalStudents]
VAR __level2_code =
CALCULATE ( [TotalStudents], ALL ( 'Table'[Class], 'Table'[Year] ) )
VAR __level3_code =
CALCULATE (
[TotalStudents],
ALL ( 'Table'[Class], 'Table'[Level], 'Table'[Year] )
)
VAR __level4_code =
CALCULATE (
[TotalStudents],
ALL ( 'Table'[Class], 'Table'[Level], 'Table'[School], 'Table'[Year] )
)
VAR __result =
SWITCH (
TRUE (),
__level1, __level1_code,
__level2, __level2_code,
__level3, __level3_code,
__level4, __level4_code
)
RETURN
__result

Final output:

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.