Incorrect % of Parent Row in Dynamic Hierarchy Using SELECTEDVALUE in DAX

[sagarlothe85]

Hi Power BI Community,

I'm building a matrix visual where I show the % Student Count by Hierarchy based on a dynamic slicer (Parameter1 Student Related Data) that maps to multiple fields (e.g., Gender, Grade, Locality, etc.). The hierarchy looks like this:

The goal is:

At Year level → Show student count as a % of the Grand Total.

At Parameter level under each year → Show student count as a % of the Parent Year.

Grand Total row → Should always display 100%.

Here's the current DAX I’m using:

% Student Count by Hierarchy =
VAR CurrentCount = [Student Count]
VAR GrandTotal =
CALCULATE(
[Student Count],
REMOVEFILTERS('DimScholasticYears'),
REMOVEFILTERS('Parameter1 Student Related Data')
)
VAR CurrentYear = SELECTEDVALUE('DimScholasticYears'[Scholastic_Year])
VAR IsParameterLevel =
NOT ISBLANK(CurrentYear) &&
ISINSCOPE('Parameter1 Student Related Data'[Parameter1 Student Data Fields])
VAR IsYearLevel =
NOT ISBLANK(CurrentYear) &&
NOT ISINSCOPE('Parameter1 Student Related Data'[Parameter1 Student Data Fields])
VAR ParentContext =
IF(
IsParameterLevel,
CALCULATE(
[Student Count],
REMOVEFILTERS('Parameter1 Student Related Data'),
'DimScholasticYears'[Scholastic_Year] = CurrentYear
)
)
RETURN
IF(
ISBLANK(CurrentYear),
1,
IF(
IsParameterLevel,
DIVIDE(CurrentCount, ParentContext, 0),
DIVIDE(CurrentCount, GrandTotal, 0)
)
)
The DAX runs, and structure-wise it’s fine — but the values are incorrect. For example, in one breakdown:

Child row value shows 7.18%

But it should actually be 9.03%, based on the actual year total.

I’ve tried variations using ALL, KEEPFILTERS, and REMOVEFILTERS, but can’t get the correct context isolation.

Any help to get this working with accurate parent-child % in a dynamic hierarchy would be greatly appreciated!

Thanks in advance!

[v-achippa · Accepted solution]

Hi @sagarlothe85,

Unfortunately Power BI does not currently support calculating % of parent in a fully dynamic hierarchy of variable depth built using field parameters without using ISINSCOPE.

Without ISINSCOPE DAX cannot reliably determine if you are at the Year, Gender or Type level.

DAX has no built in method to detect hierarchy depth or move up the parent unless you hardcode.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[sagarlothe85]

This is the sample dataset and snapshots of the DAX and Visuals attached. We can't use ISINSCOPE function due to it's limitations. See the snapshots I've attached, one is giving correct % COUNT while other is not.

[v-achippa · Accepted solution]

Hi @sagarlothe85,

Unfortunately Power BI does not currently support calculating % of parent in a fully dynamic hierarchy of variable depth built using field parameters without using ISINSCOPE.

Without ISINSCOPE DAX cannot reliably determine if you are at the Year, Gender or Type level.

DAX has no built in method to detect hierarchy depth or move up the parent unless you hardcode.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @sagarlothe85,

As we haven’t heard back from you, we wanted to kindly follow up to check if the issue is resolved?
If any response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa · Accepted solution]

Hi @sagarlothe85,

We wanted to kindly follow up to check if the issue is resolved?
If any response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[sagarlothe85]

See, in the third column I'm getting the correct % value for Year. But if i break down further by Gender and Type. The % value for (female) gender is showing as 5.67% which is incorrect, the correct value should be 48.79%.

Also breaking down further by Type, again the % values are not correct showing as 5.67% while the correct values are 45.66%.

I want the parent row should be % of Grand Total and every child row should be % of their respective Parent row. 

[v-achippa]

Hi @sagarlothe85,

Here is the corrected measure that calculates each level as a % of its immediate parent dynamically and it works correctly for multiple dynamic hierarchy levels:

% Student Count by Hierarchy =
VAR CurrentCount = [Student Count]
VAR CurrentYear = SELECTEDVALUE('DimScholasticYears'[Scholastic_Year])

VAR IsYearLevel = ISINSCOPE('DimScholasticYears'[Scholastic_Year]) &&
NOT ISINSCOPE('Parameter1 Student Related Data'[Parameter1 Student Data Fields])

VAR IsParameterLevel = ISINSCOPE('DimScholasticYears'[Scholastic_Year]) &&
ISINSCOPE('Parameter1 Student Related Data'[Parameter1 Student Data Fields])

VAR GrandTotal =
CALCULATE([Student Count],
REMOVEFILTERS('DimScholasticYears'),
REMOVEFILTERS('Parameter1 Student Related Data')
)

VAR ParentContext =
IF(IsParameterLevel,
CALCULATE([Student Count],
REMOVEFILTERS('Parameter1 Student Related Data'),
KEEPFILTERS('DimScholasticYears')
),
GrandTotal
)

RETURN
SWITCH(
TRUE(),
ISBLANK(CurrentYear), 1, 
IsParameterLevel, DIVIDE(CurrentCount, ParentContext, 0),
IsYearLevel, DIVIDE(CurrentCount, GrandTotal, 0),
BLANK()
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[sagarlothe85]

% Student Count by Hierarchy TB3 (Student Data) =
VAR CurrentLevelValue = COUNT('Student Data (Yearly Student)'[ID])
VAR ParentLevelValue =
    SWITCH(
        TRUE(),
        ISINSCOPE('Student Data (Yearly Student)'[All Records]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('Student Data (Yearly Student)'[All Records])),
        ISINSCOPE('Student Data (Yearly Student)'[Ever_Had_Level_Of_Support]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('Student Data (Yearly Student)'[Ever_Had_Level_Of_Support])),
        ISINSCOPE('Student Data (Yearly Student)'[Was_in_Good_Shepherd]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('Student Data (Yearly Student)'[Was_in_Good_Shepherd])),
        ISINSCOPE('Student Data (Yearly Student)'[Was_In_Induction_Class]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('Student Data (Yearly Student)'[Was_In_Induction_Class])),
        ISINSCOPE('Student Data (Yearly Student)'[Was in Scheme 9?]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('Student Data (Yearly Student)'[Was in Scheme 9?])),
        ISINSCOPE('Student Data (Yearly Student)'[Was_In_Virtual_School]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('Student Data (Yearly Student)'[Was_In_Virtual_School])),
        ISINSCOPE('DimAssessmentComponent'[Assessment_Component]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimAssessmentComponent'[Assessment_Component])),
        ISINSCOPE('DimAssessmentType'[Assessment_Type]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimAssessmentType'[Assessment_Type])),
        ISINSCOPE('DimChosenIn'[Chosen_In]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimChosenIn'[Chosen_In])),
        ISINSCOPE('DimClassLevel'[Class_Level]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimClassLevel'[Class_Level])),
        ISINSCOPE('DimSchoolNames'[College]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSchoolNames'[College])),
        ISINSCOPE('DimGender'[Gender]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimGender'[Gender])),
        ISINSCOPE('DimSchoolNames'[Latest_School_Name]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSchoolNames'[Latest_School_Name])),
        ISINSCOPE('DimLevelofSupport'[Level_Of_Support]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimLevelofSupport'[Level_Of_Support])),
        ISINSCOPE('DImMQFLevel'[MQF_Level]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DImMQFLevel'[MQF_Level])),
        ISINSCOPE('DimNationalities'[Nationality]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimNationalities'[Nationality])),
        ISINSCOPE('DimNationalities'[Nationality_Category]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimNationalities'[Nationality_Category])),
        ISINSCOPE('DimPaperTypeandLevel'[Paper_Type_And_Level]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimPaperTypeandLevel'[Paper_Type_And_Level])),
        ISINSCOPE('DimSubjectTrack'[Previous_Track]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSubjectTrack'[Previous_Track])),
        ISINSCOPE('DimClassLevel'[School_Level]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimClassLevel'[School_Level])),
        ISINSCOPE('DImSchoolLocalities'[School_Locality]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DImSchoolLocalities'[School_Locality])),
        ISINSCOPE('DImSchoolLocalities'[School_Locality_Region]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DImSchoolLocalities'[School_Locality_Region])),
        ISINSCOPE('DimSchoolNames'[School_Name]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSchoolNames'[School_Name])),
        ISINSCOPE('DimScholasticYears'[Scholastic_Year]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimScholasticYears'[Scholastic_Year])),
        ISINSCOPE('DimSpecialHelporAssistance'[Special_Help_Or_Assistance]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSpecialHelporAssistance'[Special_Help_Or_Assistance])),
        ISINSCOPE('DimStudentLocalities'[Student_Locality]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimStudentLocalities'[Student_Locality])),
        ISINSCOPE('DimStudentLocalities'[Student_Locality_Region]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimStudentLocalities'[Student_Locality_Region])),
        ISINSCOPE('DimSubjectClassLevel'[Subject_Class_Level]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSubjectClassLevel'[Subject_Class_Level])),
        ISINSCOPE('DimSubjectCurriculum'[Subject_Curriculum]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSubjectCurriculum'[Subject_Curriculum])),
        ISINSCOPE('DimSubject'[Subject_Name]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSubject'[Subject_Name])),
        ISINSCOPE('DimSubject'[Subject_School_Level]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSubject'[Subject_School_Level])),
        ISINSCOPE('DimSubjectType'[Subject_Type]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSubjectType'[Subject_Type])),
        ISINSCOPE('DimAssessmentandExamTerm'[Term]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimAssessmentandExamTerm'[Term])),
        ISINSCOPE('DimSubjectTrack'[Track]),
            CALCULATE(COUNT('Student Data (Yearly Student)'[ID]), REMOVEFILTERS('DimSubjectTrack'[Track])),
        -- Default fallback: grand total
        CALCULATE(COUNT('Student Data (Yearly Student)'[ID]))
    )
RETURN
DIVIDE(CurrentLevelValue, ParentLevelValue)

This is the DAX which is giving me correct results, but now the issue is if I'm selecting Gender and Nationality Category I'm not getting correct % because in the DAX using ISINSCOPE I've written Gender over Nationality Category.
This is a limitation of ISINSCOPE, I want to make it fully dynamic. How should i do it?

[v-achippa]

Hi @sagarlothe85,

Please try using this below measure it detects the current level and calculates % of parent or % of total accordingly:

% Student Count by Hierarchy =

VAR CurrentCount = COUNT('Student Data (Yearly Student)'[ID])

VAR IsYearLevel = ISINSCOPE('DimScholasticYears'[Scholastic_Year])

VAR IsOtherLevel = ISINSCOPE('Parameter Table'[Field]) 

VAR GrandTotal =

    CALCULATE(

        COUNT('Student Data (Yearly Student)'[ID]),

        REMOVEFILTERS('DimScholasticYears'),

        REMOVEFILTERS('Parameter Table')

    )

VAR ParentTotal =

    IF(

        IsOtherLevel,

        CALCULATE(

            COUNT('Student Data (Yearly Student)'[ID]),

            REMOVEFILTERS('Parameter Table'),

            KEEPFILTERS('DimScholasticYears')

        ),

        GrandTotal

    )

RETURN

SWITCH(

    TRUE(),

    NOT IsYearLevel, 1,

    IsOtherLevel, DIVIDE(CurrentCount, ParentTotal),

    IsYearLevel, DIVIDE(CurrentCount, GrandTotal),

    BLANK()

)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[sagarlothe85]

No, still not getting the correct % COUNT. The year row should show the % of GT while any other selected parameter should show the % Count of the parent row. It should be dynamic and also number of selected parameter can vary.

[v-achippa]

Hi @sagarlothe85 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[v-achippa]

Hi @sagarlothe85,

Thank you for reaching out to Microsoft Fabric Community.

Thank you for the detailed explanation, the issue here is due to incorrect context isolation when calculating the parent total for your dynamic hierarchy. 

Here is the revised DAX measure that should work correctly in your matrix visual:

% Student Count by Hierarchy =
VAR CurrentCount = [Student Count]
VAR CurrentYear = SELECTEDVALUE('DimScholasticYears'[Scholastic_Year])

VAR GrandTotal = CALCULATE([Student Count], REMOVEFILTERS('DimScholasticYears'), REMOVEFILTERS('Parameter1 Student Related Data'))

VAR ParentContext = CALCULATE([Student Count], ALLEXCEPT('DimScholasticYears', 'DimScholasticYears'[Scholastic_Year]))

RETURN SWITCH(

TRUE(),
ISBLANK(CurrentYear), 1,
ISINSCOPE('Parameter1 Student Related Data'[Parameter1 Student Data Fields]), DIVIDE(CurrentCount, ParentContext, 0),
ISINSCOPE('DimScholasticYears'[Scholastic_Year]), DIVIDE(CurrentCount, GrandTotal, 0),
BLANK()
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa