Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
SamChan
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

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

1 ACCEPTED SOLUTION
v-yifanw-msft
Community Support
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])

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

vyifanwmsft_0-1713257682027.png

Final output:

vyifanwmsft_1-1713257833409.png

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.

View solution in original post

1 REPLY 1
v-yifanw-msft
Community Support
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])

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

vyifanwmsft_0-1713257682027.png

Final output:

vyifanwmsft_1-1713257833409.png

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.