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

by Inforiver | Dec 24, 2021 | ,

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

Inforiver delivers a similar spreadsheet-like Group functionality right inside Microsoft Power BI.

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

Grouping Rows - Adjusting Aggregation

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 or measures in Power BI

Watch the quick video here on grouping rows & columns.

Learn more about Inforiver's reporting capabilities.

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



Inforiver Logo
About Inforiver!

Inforiver delivers the fastest way to build collaborative reports in Power BI through a seamless low-code / no-code experience. The product is developed by Lumel Technologies, which has a decade of experience in building visualization products including ValQ, xViz and BI Hub.
ValQ
BI Hub
xViz
5920 Windhaven Pkwy, #130
Plano TX 75093
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram