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

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

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

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.

Watch the quick video here on grouping rows & columns.

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.