Forum Discussion

SamChan's avatar
SamChan
Regular Visitor
2 years ago
Solved

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

YearClassLevelSchoolRegionTotal StudentsScore
2023Class AMediorSchool XRegion 1257.5
2023Class BSeniorSchool YRegion 2308.2
2023Class CJuniorSchool ZRegion 1206.9
2023Class DMediorSchool XRegion 2277.8
2023Class ESeniorSchool YRegion 1328.5
2023Class FMediorSchool ZRegion 3227.0
2023Class GSeniorSchool XRegion 1288.1
2023Class HJuniorSchool YRegion 2186.7
2023Class IMediorSchool ZRegion 3297.9
2023Class JSeniorSchool XRegion 2268.3
2024Class KMediorSchool YRegion 1217.2
2024Class LSeniorSchool ZRegion 2318.6
2024Class MJuniorSchool XRegion 1237.3
2024Class NMediorSchool YRegion 3338.7
2024Class OSeniorSchool ZRegion 1247.4
2024Class PJuniorSchool XRegion 3348.8
2024Class QMediorSchool YRegion 2196.8
2024Class RSeniorSchool ZRegion 3358.9
2024Class SJuniorSchool XRegion 2369.0
2024Class TMediorSchool YRegion 1379.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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.