Conditional formatting of multiple hierarchies

Related to Power BI Ideas - https://ideas.powerbi.com/ideas/idea/?ideaid=5f1ab43a-2137-4cec-a040-0d5ec3511e93 

Often times in a Power BI matrix, we would like to apply conditional formatting to specific levels in a hierarchy. 

Let us take the example below which features sales by Country and Product type in rows, and Segment in columns. We would like to conditionally format any products with sales > 2.5M in blue.   

You can implement the above in Power BI using the native matrix. However, an issue would arise when you collapse the hierarchy. The rule now gets applied to all the countries, and all the values (aggregated at the country level) that are above 2.5M get highlighted.  

How do you implement a conditional formatting rule only at a specific level in a hierarchy?

You can do it using Inforiver as follows.

1. First, we will insert a new measure (a separate column) which will be populated with hierarchy level of each row. This column can be named Level. In the formula editor to create a calculated column, use the keyword LEVEL which will return the hierarchy level of each row. 

When you click on Create, Inforiver populates the Level column. Note that this column can be hidden and it need not show up in the final report.

2. In the toolbar, select Conditional Formatting --> Create Rule: 

 3. In the property pane that appears, select Rules (If Conditions) for the ‘Format by’ property. This tells Inforiver to create a rule-based conditional formatting to the field ‘Gross Sales’ (also shown in the pane).

Note that the ‘Row hierarchy levels’ is set to "Values only”. This means the format will not apply to subtotals & totals.  

4. Once the Format by property is set to "Rules (If Conditions)”, a rule editor appears below. Set up the rule for Gross Sales >2.5 million as shown below. 

5. We want this to apply only to specific levels in the hierarchy. To achieve this, we need to add another condition. Click on +Add Condition: 

6. Key in the second condition as show below and click on Apply. 

7. You can now hide the Level column by accessing the Manage Columns dropdown. 

***

Following are additional examples of level-specific conditional formatting that you can perform in Inforiver.

Font Styling (Italics, Color) for multiple levels

Icons for the hierarchy Level 3 only 

Try out more such conditional formatting options by downloading a free trial of Inforiver today.