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

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:

Line Chart in Power BI, Total Sales by Year

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

Adding Average Line to Line Chart in Power BI

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.

Power BI, Drilling Donw in Line Chart

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”
Detecting Year, Semester, Quarter, Month and Day hierarchy levels with ISINSCOPE in Power BI Desktop

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:

Hierarchy Levels in Power BI Desktop

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.

Detecting Hierarchy Level with SWITCH, TRUE and ISINSCOPE Functions in DAX

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")
            )
        )
    )
)
Detecting Hierarchy Level with IF and ISINSCOPE Functions in DAX

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])
    )
Hierarchy Levels and Average of Hierarchy Levels with DAX in Power BI Desktop

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]
    )
Sales Average per Hierarchy with DAX in Power BI Desktop

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
Showing Total Sales by Calendar Hierarchy with Bar Chart in Power BI Desktop
  • 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
Conditional formatting bar chart using measures in Power BI Desktop

Here is what you get:

Highlighting Sales below average in hierarchies in Power BI Desktop

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”.

Enabling average line in bar chart in Power BI Desktop

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

Calculating Sales below average with conditional formatting in Bar Chart in Power BI Desktop

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.

Turning off "Drill down"

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.

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

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

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

Any thoughts? Share it with us here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.