Decomposition tree of percentage on condition and in tabular hierarchy view

[Deepu_s]

Hi all , Needed help!!
Here there is two tables Teachers and Students

Teachers table

ID                Title                                                  Linked                                                                                                                                               Status                                       Type                  
odbc-449   School Administration Hierarchy     is parent of: odbc-450                                                                                                                   In Progress                                School               
odbc-450   Academic Department Structure     has parent: odbc-449, is parent of: odbc-963, is parent of:odbc-451                                            In Progress                               Department         
odbc-963   Subject Offerings Overview             has parent:odbc-450, is parent of: odbc-965, is parent of: odbc-964                                           Definition in progress               Subject               
odbc-451   Educational Curriculum Structure    has parent:odbc-450, is parent of: odbc-452                                                                                Ready to read                            Subject               
odbc-940   School Organization Chart               is parent of: odbc-941                                                                                                                  Draft                                           School             
odbc-941   Academic Department Breakdown  has parent: odbc-940, is parent of: odbc-895, is parent of: odbc-893, is parent of:odbc-891     Open                                          Department         
odbc-891   Sequential Learning Modules          has parent: odbc-941, is parent of: odbc-892                                                                               New                                           Subject                 
odbc-895   Subject Offerings Overview             has parent: odbc-941                                                                                                                     New                                           Subject            
odbc-893   Subject Offerings Overview             has parent: odbc-941, is parent of: odbc-894                                                                                New                                           Subject 

Students table

ID             Type                  Title                                                          State            Parent
130           Subject              Educational Curriculum Structure            New
146           Module             Learning Pathway Framework                  New                 130
1377         Chapter             Academic Content Organization              Done               146
2079         Chapter             Course Outline Design                             Done               146
132           Subject              Sequential Learning Modules                  New
150           Module              Subject-Based Lesson Plan                      New                132
246           Chapter             Curriculum Development Framework      Done               150
248           Chapter             Academic Program Structure                   Done               150
247           Chapter             Educational Module Layout                     Done               150
249           Chapter             Chapter Progression Template                 Done               150

Tried Creating a tree in  where  
     School Title                                              =over all percentage 
            Department Title                                   Over all Percentage
                      Subject  Title                               Over all percentage 
                              Module Title                        %
                                     Chapter  Title                 actual percentage where shows all the Done's and not Done's

Where by calculating the percentage of the Students table cloumn State  Number of "Done" by count of Column Type "Chapter"
                               The dax for calculating is 

Percentage Done =

VAR TotalChapters = CALCULATE(COUNTROWS('Students'), 'Students'[Type] = "Chapter")

VAR DoneChapters = CALCULATE(COUNTROWS('Students'), 'Students'[State] = "Done", 'Students'[Type] = "Chapter")

RETURN

IF(

    ISBLANK(MAX('Students'[Subject Title])),

    0,

    IF(

        ISBLANK(MAX('Teachers'[Subject Title])),

        0,

        IF(

            TotalDones = 0,

            0,

            DoneChapters / TotalChapters

        )

    )

)

But in the tree i am not getting the order flow where under "School" ,"Department" i am getting every Subject  that is not under the  Perticular department also i have the given relationship for Calulated Subject title column that is many to many relationship 
And if the School is not having any Department aslo that should be shown with 0 or blank this is not happening  in this case
Help needed!! with the relationship of table and the percentage
And in decomposition Tree Chart

@Ashish_Mathur 

[v-jialongy-msft]

Hi @Deepu_s 

For hierarchical data such as yours (School, Department, Subject, Module, Chapter), it's crucial that the relationships between these entities are correctly set up to allow proper filtering and aggregation:

Ensure Correct Relationships: Your relationships should typically be one-to-many from the higher hierarchy (e.g., School) to the lower hierarchy (e.g., Department, Subject). If you're using a many-to-many relationship, ensure it is truly needed (often it is not and can complicate your model).

Use Star Schema Where Possible: Centralize your fact table (e.g., Students table) and have dimension tables (School, Department, Subject, Module, Chapter) linking directly or indirectly to it. This simplifies the flow of filters and aggregations.

DAX Calculation Adjustments:

Based on your DAX formula, here’s a revised version with some explanations and possible corrections:

Percentage Done = 
VAR TotalChapters = CALCULATE(
    COUNTROWS(Students), 
    Students[Type] = "Chapter"
)
VAR DoneChapters = CALCULATE(
    COUNTROWS(Students), 
    Students[State] = "Done", 
    Students[Type] = "Chapter"
)
RETURN 
IF(
    TotalChapters = 0,  // Changed condition to check TotalChapters directly
    0, 
    DIVIDE(DoneChapters, TotalChapters)  // Using DIVIDE for safe division
)

Best Regards,

Jayleny

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

[Deepu_s]

Hi Jayleny ,
There is no link in between tables where i could make the fact tables and dim tables for star schema and tried to give relationships there is no heirachey coming as accepted and if any way for merging the tables it could or could help in dummy pbix file for higher understanding! 
 
Regards