Adding comments on aggregated rows

In many cases, you may need to add comments not only to the most detailed rows of data but also to aggregated rows, such as Account Group. This can be challenging because aggregated rows typically don’t have unique IDs on the same granularity as the corresponding key field in your storage table, making it difficult to write data to them.

Let’s take an example with the following account structure:

When flattened, you’ll notice there are no rows for “Engineering” or “Office” where values can be stored.

To add data to aggregated rows, we first need to give them unique identifiers. Since the data in this example is loaded from a file, we’ve added the group values as extra rows in the source data. Alternatively, you could use Power Query to generate these additional rows.

We’ve also introduced a Level column, which distinguishes between different levels of the hierarchy. While this example uses two levels, the approach can easily be extended to accommodate any number of levels.

In the screenshot above, the Show items with no data- option has been enabled. This setting is generally discouraged when using the Planning & Reporting visual. A better approach is to use a measure that forces specific rows to be displayed:

When the steps above have been done, we can start to configure our Planning & Reporting visual.

To do this, we'll pull in our account hierarchy on rows. For this case we'll just display two columns; the actuals of the year and the comment for said actuals. As expected, we will see our account hierarchy with the extra rows we added.

At this stage, comments can be added to the individual rows as usual. The display will show comments at the lowest level, but the top-level aggregation will display as "..." since the underlying values vary across the lower-level rows.

To display the higher level comment for the group row itself (rather than as "..."), apply a Value Filter at the group level.

To do this, go to the group level properties, scroll down, and find the "Advanced Filters" menu.

Open the menu, click "Add Filter" under value filters, and select the comment field.

Configure the filter to display only the grouped level.

Now, you will see your comment displayed at both the lowest level and the group level. You can also add comments directly at the "group level"- row on the lower level row, and they will only apply to the aggregated row.

In most cases, you don’t want the group level (Engineering and Office in the case above) to be visible to the end user for the lower level (account level in the case above). To hide them, apply a Dimension Filter.

Dimension filters works in a unique way as they control whether a row is displayed, without affecting the data contained in the row. This is particularly useful when working with multiple hierarchy levels.

You’ll find dimension filters below the value filters. In this example, configure a filter for the lowest level (Account) to hide the aggregated rows.

And there you have it! You can now add comments to both the lowest and aggregated levels. 🎉



Before wrapping up, there’s one final detail to address. So far, we’ve only applied filters to the comment column. While this works well for reporting, it can cause issues when dealing with planning numbers.

If values are entered at an aggregated level, they will be applied to the identifier of that aggregated row (Office below), which can be confusing as that row is not visible. In the example below, the value 1,000 is splashed on the two underlying keys, Office and 5011 Electricity, but as we have hidden the dimension Office by filtering out group dimensions, Office is not displayed, even if it is included in the input.

To fix this, add a column filter so that numerical values are only displayed at the lowest level.