Highlighting Below Avg Sales per Hierarchy Level with SWITCH() and ISINSCOPE() DAX Functions in Power BI

I was working on a project a wee bit ago that the customer had conditional formatting requirement on a Column Chart.
They wanted to format the columns in the chart conditionally based on the average value based on the level of hierarchy you are at.
Here is the scenario, I have a Calendar hierarchy as below:

• Calendar Hierarchy:
  • Year
  • Semester
  • Quarter
  • Month
  • Day

I use “Adventure Works DW2017, Internet Sales” Excel as my source in Power BI Desktop. If I want to visualise “Total Sales” over the above “Calendar Hierarchy” I get something like this:

Now I activate “Average Line” from “Analytics” tab of the Line chart.

When I drill down in the line chart the Average line shows the average of that particular hierarchy level that I am in. This is quite cool that I get the average base on the level that I’m in code free.

Easy, right?

Now, the requirement is to show the above behaviour in a “Column Chart” (yes! visualising time series with column chart, that’s what the customer wants) and highlight the columns with values below average amount in Orange and leave the rest in default theme colour.

So, I need to create Measures to conditionally format the column chart. I also need to add a bit of intelligent in the measures to:

• Detect which hierarchy level I am in
• Calculate the average of sales for that particular hierarchy level
• Change the colour of the columns that are below the average amount

Let’s get it done!

Detecting Hierarchy Level with ISINSCOPE() DAX Function

Microsoft introduced ISINSCOPE() DAX function in the November 2018 release of Power BI Desktop. Soon after the announcement “Kasper de Jonge” wrote a concise blogpost about it.

So I try to keep it as simple as possible. Here is how is works, the ISINSCOPE() function returns “True” when a specified column is in a level of a hierarchy. As stated earlier, we have a “Calendar Hierarchy” including the following 5 levels:

• Year
• Semester
• Quarter
• Month
• Day

So, to determine if we are in each of the above hierarchy levels we just need to create DAX measures like below:

ISINSCOPE Year		=	ISINSCOPE('Date'[Year])

ISINSCOPE Semester	=	ISINSCOPE('Date'[Semester])

ISINSCOPE Quarter	=	ISINSCOPE('Date'[Quarter])

ISINSCOPE Month		=	ISINSCOPE('Date'[Month])

ISINSCOPE Day		=	ISINSCOPE('Date'[Day])

Now let’s do an easy experiment.

• Put a Matrix on the canvas
• Put the “Calendar Hierarchy” to “Rows”
• Put the above measures in “Values”

As you see the “ISINSCOPE Year” shows “True” for the “Year” level. Let’s expand to the to the next level and see how the other measures work:

Consolidating Measures in One Measure

Now that we see how ISINSCOPE() function works, let’s take another step further and see how we can consolidate all measures into just one measure. Remember, our scenario is to calculate Average values for each hierarchy level. I use a combination of “SWITCH()“, “TRUE()” and “ISINSCOPE()” functions to identify each level. There is a caveat in using the combination of the three functions that I explain.

Here is what we want achieve in this section. We want to be able to show the hierarchy level in a Matrix visual. To do so we use “SWITCH()” function as below:

• If hierarchy level is Year then show “Year”
• If hierarchy level is Semester then show “Semester”
• If hierarchy level is Quarter then show “Quarter”
• If hierarchy level is Month then show “Month”
• If hierarchy level is Day then show “Day”

Let’s replicate the above in DAX. Something like this may work right?

Hierarchy Level = 
SWITCH(
    TRUE()
        , ISINSCOPE('Date'[Day]), "Day"
        , ISINSCOPE('Date'[Month]), "Month"
        , ISINSCOPE('Date'[Quarter]), "Quarter"
        , ISINSCOPE('Date'[Semester]), "Semester"
        , ISINSCOPE('Date'[Year]), "Year"
        , "Other"
    )

As per the documentation of the “SWITCH()” function the above expression must work like this:

Evaluate logical “TRUE()” against a list of values which are the ISINSCOPE() functions and return ONE of multiple result expressions. Therefore, when we use the above measure in a Matrix with the “Calendar Hierarchy” we’ll get to detect each hierarchy level in one single measure.

As you see we correctly detected the hierarchy levels in a single measure. Here is the caveat, we have to create a list of values in reverse order as we see in the our hierarchy. So, “Day” in “Calendar Hierarchy” is level 5 and “Year” is level 1, therefore, we start with “Day” when we write our SWITCH() function. If we want to write the above measure with IF() we’ll have something like below:

Hierarchy Level with IF = 
IF(ISINSCOPE('Date'[Day]), "Day"
    , IF(ISINSCOPE('Date'[Month]), "Month"
        , IF(ISINSCOPE('Date'[Quarter]), "Quarter"
            , IF(ISINSCOPE('Date'[Semester]), "Semester"
                , IF(ISINSCOPE('Date'[Year]), "Year", "Other")
            )
        )
    )
)

Calculate Average of Sales Hierarchy Levels

The next step is to calculate Average Sales for each hierarchy level as below:

Daily Avg = 
AVERAGEX(
    ALL('Date'[Date])
    , [Total Sales]
    )

Monthly Avg = 
CALCULATE(
    AVERAGEX(
        ALL('Date'[Year], 'Date'[Month], 'Date'[MonthNumberOfYear])
        , [Total Sales]
        )
    , ALLEXCEPT('Date', 'Date'[Year], 'Date'[Month], 'Date'[MonthNumberOfYear])
    )

Note that I used ‘Date'[Month] along with ‘Date'[MonthNumberOfYear] in both ALL and ALLEXCEPT functions. The reason for that is that I sorted ‘Date'[Month] column by ‘Date'[MonthNumberOfYear]. Learn more about potential side effects of sorting a column by another column here.

Quarterly Avg = 
CALCULATE(
    AVERAGEX(
        ALL('Date'[Year], 'Date'[Quarter])
        , [Total Sales]
        )
    , ALLEXCEPT('Date', 'Date'[Year], 'Date'[Quarter])
    )

Semesterly Avg = 
CALCULATE(
    AVERAGEX(
        ALL('Date'[Year], 'Date'[Semester])
        , [Total Sales]
        )
    , ALLEXCEPT('Date', 'Date'[Year], 'Date'[Semester])
    )

Yearly Avg = 
CALCULATE(
    AVERAGEX(
        ALL('Date'[Year])
        , [Total Sales]
        )
    , ALLEXCEPT('Date', 'Date'[Year])
    )

Now we need to create another measure like the “Hierarchy Level” measure we created earlier using SWITCH(), TRUE() and ISINSCOPE() functions so it shows “Sales Average” for each relevant hierarchy level. The measure looks like below:

Average Sales by Hierarchy Level = 
SWITCH(TRUE()
        , ISINSCOPE('Date'[Day]), [Daily Avg]
        , ISINSCOPE('Date'[Month]), [Monthly Avg]
        , ISINSCOPE('Date'[Quarter]), [Quarterly Avg]
        , ISINSCOPE('Date'[Semester]), [Semesterly Avg]
        , ISINSCOPE('Date'[Year]), [Yearly Avg]
    )

Creating Conditional Formatting Measure

The last piece of the puzzle is to create a measure that we’re going to use to format our column chart conditionally. The below measure determines if the “Sales” is below “Average Sales by Hierarchy Level” then returns “Orange” else it does nothing.

Column Chart Avg Conditional Formatting = 
SWITCH(
    TRUE()
    , ISBLANK([Total Sales]), BLANK()
    , [Total Sales] < [Average Sales by Hierarchy Level], "Orange"
    , BLANK()
)

Now we’re all set. The only remaining part is to use the above measure to conditionally format a column chart that shows “Sales” Over “Calendar Hierarchy”.

• Put a Column Chart on the report page
• Put “Total Sales” to “Values”
• Put “Calendar Hierarchy” to Axis

• Expand “Data colour” from “Format” tab from “Visualisations” Pane
• Hover over default colour
• Click ellipsis button
• Click “Conditional Formatting”
• Select “Field Value” from “Format by” dropdown
• Select the latter measure we created from the “Based on field” section then click OK

Here is what you get:

As you can see we determined Sales below average based on hierarchy level we are at. To make this even better we can enable an average line in the bar chart. This can be done from the “Analytics” tab and enabling “Average line”.

Now if you expand down to the other levels you can quickly see the when you have Sales below average.

Note: The above measure used in the conditional formatting of the Bar Chart DOESN’T work if you enable “Drill down” as it puts filters on the selected item that you drilled down. So you’d be better to disable “Drill down” button from the “Visual Header” settings.

Note: This only affects the reader view when the report is published to Power BI Service, therefore, you cannot see its effect in Power BI Desktop.

Have you used this method before? Do you know a better way to tackle this? Please let us know in the comments section below.