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

Group rows or columns in table / matrix reports in Power BI

by Inforiver | Dec 24, 2021 | ,

Inforiver delivers a variety of spreadsheet-like capabilities that make it very appealing for Excel fans to embrace Power BI.

A very popular feature of Excel that is missing in Power BI is the ability to group rows and columns. In Excel, this is accomplished using the Group (outline) settings property.

Group rows or columns in Excel

Group rows in Power BI table / matrix reports

In the sample report shown below, the first four detail records (Projectors & Screens, Laptops, Lamps and Air Conditioners) are grouped to create a new row titled 'Segment A - Cash Flow'. An expand/collapse control governs the grouped row and its children (that belong in that group). Note that Inforiver automatically aggregates the measures at the parent level. Contrast this with Excel where you will need to perform such aggregations manually.

Group rows in Power BI

While grouping is a simple & powerful feature, you also need to pay attention to the aggregation type of each measure. In the example above, Margin % field by default would have added all the four percentage values and showed up 175.7% (basically apply a summation for row aggregation).

To override this default behavior, you must use the 'Insert Formula' option to recreate that column as shown below, and choose Row aggregation type = Formula. When you choose this type Inforiver calculates the Margin % for the aggregated row by reapplying the same formula it applied to the rows (instead of adding all the individual values).

In the image below, notice the differences in the result rows at the group level for the original Margin % measure (with SUM aggregation type) and the calculated Margin % column (with Formula aggregation type).

Original Margin % measure and the calculated Margin %

Group columns in Power BI table / matrix reports

Similarly, Inforiver also allows you to group columns. This however works a little bit differently from row grouping. Instead of creating an expand/collapse outline, it pastes a row above and visually groups them. In this example below, the section 'Profitability' groups the columns Margin & Margin %. This feature is very useful while building financial statements like income statement, balance sheet or statement of cash flows.

Group columns

Watch the quick video here on grouping rows & columns.

Group rows or columns in table / matrix reports in Power BI

Curious about Inforiver? Read why Matt Allington (Self Service BI Expert + Microsoft MVP) , Excelerator BI prefers Inforiver over the Analyze in Excel capability that comes with Power BI for ad-hoc analysis.

Learn more about Inforiver's reporting capabilities.

Related Power BI Ideas: Please add feature to group columns in matrix.

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