Upcoming webinar on 'Inforiver Charts : The fastest way to deliver stories in Power BI', Aug 29th , Monday, 10.30 AM CST.    Register Now

Conditional formatting of multiple hierarchies

by Inforiver | Jan 27, 2023 |

Related to Power BI Ideas - 

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. 

New measure - Level

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.

 Level column

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

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

Format by property

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. 

Rule for Gross Sales

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: 

+Add Condition

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

Apply second condition

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

Hide the Level column dropdown
Hide the Level column


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

Font Styling (Italics, Color) for multiple levels

Conditional Formatting - Font Styling

Icons for the hierarchy Level 3 only 

Conditional Formatting - Icons

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

Share this on:

Get Inforiver brochure

Maximize your business potential with Inforiver's paginated reporting, data entry, planning & budgeting capabilities
Download now
About Inforiver!
Inforiver is the fastest way to do everything in Power BI. It enables citizen developer productivity and unleashes true self-service with our intuitive and interactive no-code data app suite for Microsoft Power BI. The product is developed by Lumel Technologies Inc, who are #1 Power BI Visuals AppSource Partner serving over 3,000+ customers worldwide with their xViz, Inforiver, and ValQ offerings.
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram