Adding rows to a table

Hi VietNor ,

To achieve your goal of having a single slicer showing both classes and schools, and to include rows with total students per school per year in your table, you can start by creating a unified dimension table that includes both class names and school names. This table can be named Dim_Combined, and it should contain one column, for example, [Slicer], with values like "Class A", "Class B", ..., "School I", "School F".

Next, in Power Query, load your original student data table which includes the columns Year, Class, and Student, and also load the Dim_School table that maps schools to their respective classes. You'll need to create a new query that merges the student table with the school mapping table. To do this, perform a left join from the student table to the Dim_School table using the Class column. This will give you a new column containing the school each class belongs to. Then, remove all rows where the school column is null, and group the resulting table by both Year and School, aggregating the sum of students.

Here's a simplified M code snippet for the transformation:

let
    Source = StudentData,
    SchoolMap = Dim_School,
    Merged = Table.NestedJoin(Source, {"Class"}, SchoolMap, {"Class"}, "SchoolTable", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "SchoolTable", {"School"}),
    Filtered = Table.SelectRows(Expanded, each ([School] <> null)),
    Grouped = Table.Group(Filtered, {"Year", "School"}, {{"Student", each List.Sum([Student]), type number}}),
    Renamed = Table.RenameColumns(Grouped, {{"School", "Class"}})
in
    Renamed

This final table has the school names in the same column as the classes, renamed to Class for compatibility. Now, go to your original student table and append this new table of school totals to it, so that your unified fact table includes both class-level and school-level data.

Then, in the data model, link the [Class] column from this unified fact table to the [Slicer] column in the Dim_Combined table. Now, using a single slicer from Dim_Combined, you can slice your visuals by either class or school, and your measures such as:

Total Students = SUM(Fact_Students[Student])

will respond appropriately. This approach avoids having two slicers and integrates school totals seamlessly into your report.

Best regards,