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:
Continue reading “Highlighting Below Avg Sales per Hierarchy Level with SWITCH() and ISINSCOPE() DAX Functions in Power BI”
