Highlighting Beneath Avg Gross sales per Hierarchy Degree with SWITCH() and ISINSCOPE() DAX Capabilities in Energy BI

I used to be engaged on a undertaking a wee bit in the past that the shopper had conditional formatting requirement on a Column Chart.
They needed to format the columns within the chart conditionally primarily based on the typical worth primarily based on the extent of hierarchy you’re at.
Right here is the state of affairs, I’ve a Calendar hierarchy as under:

• Calendar Hierarchy:
  • 12 months
  • Semester
  • Quarter
  • Month
  • Day

I take advantage of “Journey Works DW2017, Web Gross sales” Excel as my supply in Energy BI Desktop. If I wish to visualise “Complete Gross sales” over the above “Calendar Hierarchy” I get one thing like this:

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

Once I drill down within the line chart the Common line exhibits the typical of that specific hierarchy degree that I’m in. That is fairly cool that I get the typical base on the extent that I’m in code free.

Straightforward, proper?

Now, the requirement is to point out the above behaviour in a “Column Chart” (sure! visualising time collection with column chart, that’s what the shopper desires) and spotlight the columns with values under common quantity in Orange and depart the remaining in default theme color.

So, I have to create Measures to conditionally format the column chart. I additionally want so as to add a little bit of clever within the measures to:

• Detect which hierarchy degree I’m in
• Calculate the typical of gross sales for that specific hierarchy degree
• Change the color of the columns which might be under the typical quantity

Let’s get it executed!

Detecting Hierarchy Degree with ISINSCOPE() DAX Perform

Microsoft launched ISINSCOPE() DAX operate within the November 2018 launch of Energy BI Desktop. Quickly after the announcement “Kasper de Jonge” wrote a concise blogpost about it.

So I attempt to maintain it so simple as doable. Right here is how is works, the ISINSCOPE() operate returns “True” when a specified column is in a degree of a hierarchy. As said earlier, we’ve a “Calendar Hierarchy” together with the next 5 ranges:

• 12 months
• Semester
• Quarter
• Month
• Day

So, to find out if we’re in every of the above hierarchy ranges we simply have to create DAX measures like under:

ISINSCOPE 12 months		=	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 a straightforward experiment.

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

As you see the “ISINSCOPE 12 months” exhibits “True” for the “12 months” degree. Let’s broaden to the to the following degree and see how the opposite measures work:

Consolidating Measures in One Measure

Now that we see how ISINSCOPE() operate works, let’s take one other step additional and see how we are able to consolidate all measures into only one measure. Bear in mind, our state of affairs is to calculate Common values for every hierarchy degree. I take advantage of a mix of “SWITCH()“, “TRUE()” and “ISINSCOPE()” capabilities to establish every degree. There’s a caveat in utilizing the mixture of the three capabilities that I clarify.

Here’s what we wish obtain on this part. We would like to have the ability to present the hierarchy degree in a Matrix visible. To take action we use “SWITCH()” operate as under:

• If hierarchy degree is 12 months then present “12 months”
• If hierarchy degree is Semester then present “Semester”
• If hierarchy degree is Quarter then present “Quarter”
• If hierarchy degree is Month then present “Month”
• If hierarchy degree is Day then present “Day”

Let’s replicate the above in DAX. One thing like this may occasionally work proper?

Hierarchy Degree = 
SWITCH(
    TRUE()
        , ISINSCOPE('Date'[Day]), "Day"
        , ISINSCOPE('Date'[Month]), "Month"
        , ISINSCOPE('Date'[Quarter]), "Quarter"
        , ISINSCOPE('Date'[Semester]), "Semester"
        , ISINSCOPE('Date'[Year]), "12 months"
        , "Different"
    )

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

Consider logical “TRUE()” in opposition to an inventory of values that are the ISINSCOPE() capabilities and return ONE of a number of end result expressions. Subsequently, after we use the above measure in a Matrix with the “Calendar Hierarchy” we’ll get to detect every hierarchy degree in a single single measure.

As you see we accurately detected the hierarchy ranges in a single measure. Right here is the caveat, we’ve to create an inventory of values in reverse order as we see within the our hierarchy. So, “Day” in “Calendar Hierarchy” is degree 5 and “12 months” is degree 1, due to this fact, we begin with “Day” after we write our SWITCH() operate. If we wish to write the above measure with IF() we’ll have one thing like under:

Hierarchy Degree 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]), "12 months", "Different")
            )
        )
    )
)

Calculate Common of Gross sales Hierarchy Ranges

The following step is to calculate Common Gross sales for every hierarchy degree as under:

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

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

Be aware that I used ‘Date'[Month] together with ‘Date'[MonthNumberOfYear] in each ALL and ALLEXCEPT capabilities. The explanation for that’s that I sorted ‘Date'[Month] column by ‘Date'[MonthNumberOfYear]. Study extra about potential unwanted effects of sorting a column by one other column right 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 have to create one other measure just like the “Hierarchy Degree” measure we created earlier utilizing SWITCH(), TRUE() and ISINSCOPE() capabilities so it exhibits “Gross sales Common” for every related hierarchy degree. The measure appears like under:

Common Gross sales by Hierarchy Degree = 
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 final piece of the puzzle is to create a measure that we’re going to make use of to format our column chart conditionally. The under measure determines if the “Gross sales” is under “Common Gross sales by Hierarchy Degree” 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 one remaining half is to make use of the above measure to conditionally format a column chart that exhibits “Gross sales” Over “Calendar Hierarchy”.

• Put a Column Chart on the report web page
• Put “Complete Gross sales” to “Values”
• Put “Calendar Hierarchy” to Axis

• Broaden “Information color” from “Format” tab from “Visualisations” Pane
• Hover over default color
• Click on ellipsis button
• Click on “Conditional Formatting”
• Choose “Discipline Worth” from “Format by” dropdown
• Choose the latter measure we created from the “Based mostly on subject” part then click on OK

Here’s what you get:

As you possibly can see we decided Gross sales under common primarily based on hierarchy degree we’re at. To make this even higher we are able to allow a median line within the bar chart. This may be executed from the “Analytics” tab and enabling “Common line”.

Now should you broaden all the way down to the opposite ranges you possibly can rapidly see the when you’ve got Gross sales under common.

Be aware: The above measure used within the conditional formatting of the Bar Chart DOESN’T work should you allow “Drill down” because it places filters on the chosen merchandise that you just drilled down. So that you’d be higher to disable “Drill down” button from the “Visible Header” settings.

Be aware: This solely impacts the reader view when the report is printed to Energy BI Service, due to this fact, you can not see its impact in Energy BI Desktop.

Have you ever used this methodology earlier than? Are you aware a greater technique to deal with this? Please tell us within the feedback part under.

Associated