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

[SamChan]

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

[v-yifanw-msft · Accepted solution]

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])

2.Add new measures.

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:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

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

Best Regards,

Ada Wang

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