The cBI Focus Planner offers a variety of formatting and configuration options that support the targeted use of the visual in planning reports. Since many functions are based on the standard options of the Power BI standard matrix visual, this section will focus exclusively on the advanced and specific features of the cBI Focus Planner.
Filter Functions #
To effectively control data display, the cBI Focus Planner features its own filter functions, which intentionally differ from the standard filters in Power BI.
Advantage: The internal filters of the visual also consider non-displayed values when calculating sums, allowing for more realistic planning.
The filtering functionality of the cBI Focus Planner operates within the defined data set, which is determined by the report filters. These report filters have a cascading effect on the value range. The following order applies:
- Filter on all pages
- Filter on this page
- Filter on this visual
In this way, data areas can be restricted down to the visual level, ensuring that only predefined data areas are accessible to the planners.
Editors can thus specifically control which data can be accessed in the visual.
In the present example, the visual is already filtered to the category “Advisory” for simplification. (1.)
The top three rows include additional values from other categories. However, the fourth row represents the total sum of the “Advisory” category. (2.)
To demonstrate that non-displayed values are also included in the calculations, the “Asset Class” filter is now applied within the “Advisory” category. (3.)
When comparing the top four rows of both visuals, no changes are observed, even though values have now been filtered. (4.)
Application of Visual Filters:
- Open the formatting menu (“Format visual”).
- Navigate to one of the following sections:
- Value Filter Options
- Row Filter Options
- Column Filter Options
- Activate the desired filter using the corresponding toggle switch.
- In the “Value Filter Options” section, a default value (Default Selected Value) can also be defined, which will be automatically selected when the report is loaded.
- By default, “First Value” is activated, meaning the first available value in the filter list will be used.
Best Practices for Using Default Values for Value Filters
- When a value filter is hidden in the report, a default value should always be defined. This prevents unexpected write-backs of incorrect values.
- Example: A report includes a filter on the dimension “Data Type,” making only the value “Plan” available in the visual. If the default value for the column Data_Type_Name is set to “First Value,” it will correspond to the desired value “Plan” as long as the report filter is active. However, if the report filter is later removed or changed, additional values like “Actual” may become visible. In this case, “Actual” might be interpreted as the new “First Value,” potentially leading to the accidental write-back or overwriting of actual data (Actual).
- Recommendation: Explicitly define a fixed default value when the filter is not visible or should not be altered by the user. This ensures that the write-back function always operates with the correct values.
When activated, a dropdown menu is displayed in the visual, containing only those values that have been referenced in the “Build Visual” section. The value filter is characterized by its restriction to single selection.
If certain key attributes should not be visible to planners, they can be included in the “4. Filters (Dim. Table 3 to n)” section in the Build Visual and filtered accordingly.
Defining Editable Columns (Writable/Non-Writable Cells) #
To specifically control the write-back function, the “Measures” section allows you to define which columns in the fact table are editable. This ensures that only designated columns can be modified during the write-back process, enhancing data integrity and preventing unintended changes to non-editable cells.
- First, select a column under “Measure name.” The dropdown contains all columns that were added to the data role “1. Values” during modelling.
- If the selected column is from the fact table, you can choose between “Writable” and “Non-Writable.”
- The default is “Writable.”
- If the selection is a calculated measure (Calculation), you can instead perform a mapping by selecting a column from the fact table where the inputs for the selected calculation should be written.
- The default here is “Non-Writable.”
In the right example, a calculation named “Sales dep. Yield Rate” has been selected. However, there is a column in the fact table whose aggregations could not be represented with the standard aggregation options. Accordingly, a calculation was created that can be mapped to the actual column in the fact table using this option.
- (Optional) In the “Validation for data entry” section, you can set minimum and maximum values for each editable column.
Splashing #
Another feature in the cBI Focus Planner is “Splashing.” It allows for the automatic distribution of inputs to higher hierarchy levels (nodes) across all subordinate elements (leaves). The possible distribution methods are explained in detail in this section.
You can specify whether value inputs are allowed at aggregated levels or if adjustments are only permitted at the level of the lowest elements. Additionally, it is possible to predefine the applied distribution method for each column.
- Activate/Deactivate the Splashing function.
- Select the column to which the distribution method should be applied.
- Select the column to which the distribution method should be applied.
- Define the distribution method.
- With the “Ask every time” option, a dialog will appear with each input at a node, allowing you to choose between the distribution methods (see section 8.2.2).
General Formatting #
The basic formatting options in the cBI Focus Planner are aligned with the functions of the standard matrix visual, which are used to specifically highlight structures and improve the readability of reports.
This includes adjustments to fonts, cell backgrounds, text colors, as well as the design of row and column headers.
Although the functionality largely corresponds to the standard matrix visual, there are significant differences:
- Within the formatting option “Values,” the cBI Focus Planner distinguishes between editable and non-editable cells.
- Unlike the standard matrix visual, the cBI Focus Planner does not support the “switch values to row groups rather than columns” feature.
- Units and decimal places are not defined under “Specific columns,” but rather centrally in the “Data format” section of the “General” tab (see this section).
For more information on formatting in the standard matrix visual, you can refer to: Use custom format strings in Power BI Desktop – Power BI | Microsoft Learn
Subtotals #
The “Column Subtotals” option controls the display of subtotals at the column level, such as for aggregated values within a hierarchy. It allows for the visibility of partial results within column groupings.
Both the cBI Focus Planner and the standard matrix visual can fur-ther differentiate this functionality using the “Per Column Level” setting: A corresponding slicer allows for the selection of a specific column for which the subtotal display can be specifically activated or deactivated.
In contrast to the standard matrix visual, the cBI Focus Planner does not include separate configuration fields for “Values” and “Col-umns” in the “Column Subtotals” section.
Units and Decimal Places – Code Formatting #
In the “Data format” section, units can be adjusted to present numerical values consistently and clearly. The available formatting options correspond to those of the standard matrix visual and include “General,” “Whole Number,” “Decimal Number,” “Currency,” “Percentage,” “Scientific,” and “Custom.” By selecting an appropriate unit, the readability of the data, especially with large numbers, can be significantly improved.
The cBI Focus Planner also supports code-based formatting, similar to the formatting rules in Microsoft Excel. Since Power BI uses its own formatting schema, appropriate conversions should be considered when using these options.
Inserting a Code Formatting:
- Select “Format visual.”
- Go to the “General” section.
- Choose “Data format.”
- Select the “Custom” option under “Format.”
- Enter the formatting syntax.
Examples for Code Formatting:
| Unchanged Values | Values in Thousands | Values in Millions | |
| Values |  |  |  |
| Format Code |  Auto |  #.0,.00 „Tsd“ |  #.0,,.00 „M” |
For more information on formatting syntax, you can refer to the official documentation on Power BI or resources related to custom formatting in Power BI Desktop. This will provide detailed guidelines and examples for using formatting codes effectively. Use custom format strings in Power BI Desktop – Power BI | Microsoft Learn.
