Contextual Groups Table

It’s important to monitor the performance of contextual groups like boys, disadvantaged pupils, those with SEND etc, but as a rule we don’t want to have to filter down to each one in turn, it’s better to view a summary and only dig deeper if there’s a particular problem. 

However, you can’t simply throw all the factors into a table because all the groups overlap. My initial solution to this was to have lots of different tables on the page but that was hard to maintain and basically just looked a bit rubbish.

Now what I do instead is to summarise the data into a separate ContextualData table. I’m going to do a two part post on this, where here in part 1 we make the table using internal data and then in part 2 we bring in national data as a comparison.

To make life easier for me I’ve got all my student data contextual and attainment on one table (you can download my pbix here) but as the attainment data is calculated by measures this technique will work just as well if you have (as you probably will) the data in separate tables. If you want a steer towards how to calculate these KS4 measures from scratch check out my attainment 8 post here.

Goal

What I want to able to do is to stick Contextual Factor and Category into the rows of a matrix, the KS4 indicator as the columns with a measure to give me the value. So the underlying table I want looks like this and we’re going to create this with a DAX formula.

Factor	Category	Indicator	Value
Gender	F	Progress 8	x
Gender	F	Attainment 8	x
Gender	F	EBacc APS	x
Gender	M	Progress 8	x
Gender	M	Attainment 8	x
…

KS4 Indicator Values

Before we can make our contextual data table we need a table with the KS4 indicator names in it. There are several ways to get a list of static values into BI. Probably the most principled is to use Power Query/JSON and import them, but I prefer to use the DATATABLE DAX function as this makes it easier to add/edit values later on. To create a table click ‘New Table’ on the modelling tab and use the following code:

KS4Indicators = DATATABLE("Indicator",STRING,"Order",INTEGER,

                                        {{"Progress 8",1},

                                        {"Attainment 8",2},

                                        {"EBacc APS",3},

                                        {"EBacc Entry",4},

                                        {"English & Maths 5+",5}})

Obviously you can add whatever indicators you want here and call them what you like. The Order column is optional but necessary if you don’t want them to appear in alphabetical order on your visuals (as we’ll see later).

Contextual Data Table

The contextual data table is a bit more complicated and uses the following formulas:

SUMMARIZE – Summarize (as the name suggests) lets you summarise a table by a number of grouping factors so SUMMARIZE(Students,Students[Gender]) will return a two row table with a column Gender with values F and M. But unlike VALUES(Students[Gender]) (which would return the same table) when you apply a measure to the Summarised table it calculates the measure for the summarised rows – giving you a figure for whatever the measure calculates for all the female/male students.

CROSSJOIN – this joins two tables giving you every combination of their values. So if you had a table Colour, with one column Colour containing values Red,Blue and a table Car with one column Car containing values Ford, Lexus then the formula CROSSJOIN(Colour,Car) would give you

Colour	Car
Red	Ford
Red	Lexus
Blue	Ford
Blue	Lexus

ADDCOLUMNS – allows you to add columns to a table expression. You can do this within SUMMARIZE but ADDCOLUMNS gives you more flexibility over the type of columns that can be added and it has better performance so it’s always better to use it to apply aggregation columns to a SUMMARIZEd table.

UNION – joins tables together.

So what our table expression is going to do is create a series of variables that each summarise Students by a certain factor, we’re going to add a row for each KS4 Indicator then add two columns, one identifying which factor it is and the other applying the appropriate measure to calculate the value.

The code for each factor variable looks like this:

VAR Gen = ADDCOLUMNS(

                    CROSSJOIN(

                            SUMMARIZE(Students,Students[Gender]),KS4Indicators),

                    "Factor","GEN",

                    "Value",  SWITCH(TRUE(),

                                    KS4Indicators[Indicator] = "Attainment 8",[Attainment 8],

                                    KS4Indicators[Indicator] = "Progress 8",[Progress 8],

                                    KS4Indicators[Indicator] = "EBacc APS",[EBaccAPS],

                                    KS4Indicators[Indicator] = "EBacc Entry",[EBaccE],

                                    KS4Indicators[Indicator] = "English & Maths 5+",[L2Basics95])

            )

You then add below all the other factors you want, changing the three underlined parts of the code. Note that you don’t need an aggregating expression to call ‘KS4Indicators[Indicator]’ as you’re adding a calculated column within your code so row context exists.

Once you’ve added all the variables, end your table expression with UNION joining together all the different table variables (called by name).

…

RETURN

UNION(Gen,DIS,SEN,EAL,Eth)

Now we have our basic table, which put into a matrix looks like this:

So we’re on the right track but there are three things that need fixing

1.       The Order and naming of the row and column headings

2.       The formatting of the values (percentages are showing as decimals)

3.       The nonsense subtotal and total rows.

Reorder and Rename headings

There are a couple of ways to produce a custom order but both involve a separate table. (You can’t use IF statements to derive a sort value from a column then sort by that calculated column). So I made a table ContextualFactorSort which has my column names from the table we just made, what I want to display and a sort order. Again, this uses DATATABLE:

ContextFactorSort = DATATABLE("FactorID",STRING,"FactorName",STRING,"Order",INTEGER,

{

    {"GEN","Gender",1},

    {"DIS","Disadvantaged",2},

    {"SEN","Special Educational Needs and Disabilities",3},

    {"EAL","English as an Additional Language",4},

    {"Eth","Ethnicity",5}

}

)

Then select the column ‘FactorName’ in the fields list on the right hand side of the screen and, still on the modelling tab, click Sort by Column and choose order. Do the same to sort KS4Indicators[Indicator] by its order value.

Now on the modelling screen, relate these two tables to the Contextual Data table.

For the categories within each factor we’re going to use a similar but not identical approach (because it would be a pain copying out all the individual categories when I only want to change a couple). Note that these categories are contained in the column ‘ContextualData[Gender]’ – when you use UNION the column names of the first table are used but this column contains the values for Dis, SEN etc as well as M and F. Your version of this table will obviously need to account for how you label these categories, mine looks like this:

ContextCategorySort = DATATABLE("Category",STRING,"Display",STRING,"Order",INTEGER,

{

    {"M","Boys",1},

    {"F","Girls",2},

    {"TRUE","Yes",3},

    {"FALSE","No",4}

   

 }

) 

I then created a relationship between ContextCategorySort[Category] and ContextualData[Gender]. Finally I added two calculated columns to ContextualData

Category =

VAR AdjCat = RELATED(ContextCategorySort[Display])

RETURN

IF(ISBLANK(AdjCat),ContextualData[Gender],AdjCat)

CatSortOrder = RELATED(ContextCategorySort[Order])

I then sorted Category by CatSortOrder.

Now in my matrix rows I replaced ContextualData[Factor] & ContexualData[Gender] with ContextFactorSort[FactorName] and ContextualData[Category]

As the column field I replaced ContexualData[Indicator] with KS4Indicators[Indicator] which gives me this:

Formating the values

Now we’re going to sort out the formatting using the FORMAT function in a measure that will look at which indicator is selected and apply the appropriate format.

DisplayValue =

VAR Ind = SELECTEDVALUE(KS4Indicators[Order])

VAR RawValue = SELECTEDVALUE(ContextualData[Value])

RETURN

SWITCH(TRUE(),

ISBLANK(RawValue),BLANK(),

Ind = 2,FORMAT(RawValue,"###.0") ,

Ind > 3,FORMAT(RawValue,"##%"),

RawValue)

The first condition (ISBLANK..) stops rows appearing for which there’s no value. For the others I use KS4Indicators[Order] rather than KS4Indicators[Indicator] to leverage the fact that the measures for which I want to show as a percentage have order value 4 and 5.

By using SELECTEDVALUE(ContextualData[Value]) I also solve the subtotal problem as SELECTEDVALUE returns a blank if the column is not filtered to one value.

Adding a Total

To finish off we would like to show totals for all students. We can do that using the function ISINSCOPE which takes a column as an argument and returns true if that column is in the scope of the current row of the table. ISINSCOPE(ContextFactorSort[FactorName]) therefore returns true for every row of our table except the grand total row. We can use this to split our DisplayValue measure in two, with its original behaviour for the contextual data rows and new behaviour for the total row.

DisplayValue =

VAR Ind = SELECTEDVALUE(KS4Indicators[Order])

VAR RawValue = SELECTEDVALUE(ContextualData[Value])

RETURN

IF(ISINSCOPE(ContextFactorSort[FactorName]),

                                            SWITCH(TRUE(),

                                        ISBLANK(RawValue),BLANK(),

                                        Ind = 2,FORMAT(RawValue,"###.0") ,

                                        Ind > 3,FORMAT(RawValue,"##%"),

                                        RawValue),

                                        SWITCH(Ind,

                                        1,[Progress 8],

                                        2,FORMAT([Attainment 8],"###.0"),

                                        3,[EBaccAPS],

                                        4,FORMAT([EBaccE],"##%"),

                                        5,FORMAT([L2Basics95],"##%")))

Final Table