This article describes the technique to dynamically filter specific levels of a hierarchy on a dashboard and provides a method to select and visualize hierarchies on a dashboard.
This article explains how to configure the calculation of the level of a list in a hierarchy in order to apply specific calculations (custom summary) or filters by level on a dashboard.
In this example, we have an organized hierarchy of 4 levels (Org L1 to Org L4). For each item in the hierarchy, we want to calculate a module value that returns the associated level that is to be displayed on a dashboard.
Notes and Platform Context
The technique addresses a specific limitation within dashboards where a composite hierarchy's list level cannot be selected if the list is synchronized to module objects on the dashboard.
The technique uses a static module based on the levels of the composite structure used for filtering of the object on a dashboard.
The technique is based on utilizing the Summary Method "Ratio" on line items corresponding to the list levels of the composite hierarchy to define the values of the filtering line items. Note that this method is not a formula calculation, but rather a use of the Summary Method Ratio on each line item applied to the composite hierarchy.
In this example, a four-level list composite hierarchy list is used. The hierarchy in this example has asymmetrical leaf items per parent:
Defining the Level of Each List
In order to calculate the level of each item in each of the lists L1 - L4, we need to create a module that calculates the associated level of each member by this technique:
1) Create as many line items as levels of hierarchy, plus one technical line item.
2) Configure the settings in the blueprint of the line items of this filtering module, per this example and table:
Summary method Setting Ratio
Technical line item*
Level or L4 (lowest level)
L3 / Technical
L2 / Technical
L1 / Technical
L1 / Technical
When applying these settings, the filtering module looks like this:
*Note the Technical line item Summary method is using Formula. Alternatively, The Minimum Summary Method can be used but will return an error when a level of the hierarchy does not have any children and the level calculated is blank.
The filtering module with Summary method applied results:
Use the line item at the lowest level—Level (or L4) (LOWEST)—as the basis of filters or calculations.
Applying a Filter on Specific Levels in Case of Synchronization
When synchronization is enabled, the option “Select levels to show” is not available. Instead, a filter based on the level calculated can be used to show only specific levels.
In the example, we apply a filter which matches any of the level 4 and 1:
The following filtered dashboard result is achieved by using the composite hierarchy as a page selector:
Note that this article uses a planning dashboard as an example, but many of these principles apply to other types of dashboards as well.
Building a useful planning dashboard always starts with getting a set of very clear user stories, which describe how a user should interact with the system.
The user stories need to identify the following:
What the user wants to do.
What data the user needs to see to perform this action.
What data the user wants to change.
How the user will check that changes made have taken effect.
If one or more of the above is missing in a user story, ask the product owner to complete the description. Start the dashboard design, but use it to obtain the answers. It will likely change as more details arrive.
Product owners versus designers
Modelers should align with product owners by defining concrete roles and responsibilities for each team member.
Product owners should provide what data users are expecting to see and how they wish to interact with the data, not ask for specific designs (this is the role of the modelers/designers).
Product owners are responsible for change management and should be extra careful when dashboard/navigation is significantly different than what is currently being used (i.e. Excel ® ).
Pre-demo peer review
Have a usability committee that:
Is made up of modeling peers outside the project and/or project team members outside of modeling team.
Will host a mandatory gate-check meeting to review models before demos to product owners or users.
Committee is designed to ensure:
Best design by challenging modelers.
Consistency between models.
The function is clear.
Exceptions/calls to action are called out.
The best first impression.
Exception, call to action, measure impact
Building a useful planning dashboard will be successful if the dashboard allows users to highlight and analyze exceptions (issues, alerts, warning), take action and plan to solve these, and always visually check the impact of the change against a target.
Example: A dashboard is built for these two user stories that compliment each other.
Story 1: Review all of my accounts for a specific region, manually adjust the goals and enter comments.
Story 2: Edit my account by assigning direct and overlay reps.
The dashboard structure should be made of:
Dashboard header: Short name describing the purpose of the dashboard at the top of the page in "Heading 1."
Groupings: A collection of dashboard widgets.
Call to action.
Info grid(s) : Specific to one item of the main grid.
Info charts: Specific to one item of the main grid.
Specific action buttons: Specific to one item of the main grid.
Main charts: Covers more than one item of the main grid.
Individual line items: Specific to one item of the main grid, usually used for commentaries.
A dashboard can have more than one of these groupings, but all elements within a grouping need to answer the needs of the user story.
Use best judgements to determine the number of groupings added to one dashboard. A maximum of two-to-three groupings is reasonable. Past this range, consider building a new dashboard. Avoid having a "does it all" dashboard, where users keep scrolling up and down to find each section.
If users ask for a table of contents at the top of a dashboard, it's a sign that the dashboard has too much functionality and should be divided into multiple dashboards.
Call to action
Write a short sentence describing the task to be completed within this grouping. Use the Heading 2 format.
The main grid is the central component of the dashboard, or of the grouping. It's where the user will spend most of their time.
This main grid will display the KPIs needed for the task (usually in the columns) and will display one or more other dimension in the rows.
Warning: Users may ask for 20+ KPIs and need these KPIs to be broken down by many dimensions, such as by product, actual/plan/variance, or by time. It's critical to have a main grid as simple and as decluttered as possible. Avoid the "data jungle" syndrome. Users are used to "data jungles" simply because that's what they are used to with Excel.
Tips to avoid data jungle syndrome:
Make a careful KPI election (KPIs are usually the line items of a module).
Display the most important KPIs ONLY, which are those needed for decision making. Hide the others for now.
A few criteria for electing a KPI in the main grid are:
The KPI is meant to be compared across the dimension items in the rows, or across other KPIs.
Viewing the KPI values for all of the rows is required to make the decision.
The KPI is needed for sorting the rows (except on row name).
A few criteria for not electing a KPI in the main grid are (besides not matching the above criteria) when we need these KPIs in more of a drill down mode; The KPI provides valid extra info, but just for the selected row of the Dashboard and does not need to be displayed for all rows.
These "extra info" KPIs should be displayed in a different grid, which will be referred to as "info grid" in this document. Take advantage of the row/column sync functionality to provide a ton of data in your dashboard but only display data when requested or required.
Design your main grid in such a way that it does not require the user to scroll left and right to view the KPIs:
Efficiently select KPIs.
Use the column header wrap.
Set the column size accordingly.
It is ok to have users scroll vertically on the main grid. Only display 15 to 20 rows at a time when there are numerous rows, as well as other groupings and action buttons, to display on the same dashboard.
Use sorts and a filter to display relevant data.
Sort your grid
Always sort your rows. Obtain the default sort criteria via user stories. If no special sort criteria is called out, use the alphanumeric sort on the row name. This will require a specific line item.
Train end users to use the sort functionality.
Filter your grid
Ask end users or product owners what criteria to use to display the most relevant rows. It could be:
Those that make 80 percent of a total. Use the RankCumulate function.
Those that have been modified lately. This requires a process to attach a last modified date to a list item, updated daily via a batch mode.
When the main grid allows item creation, always display the newly created first.
If end users need to apply their own filter values on some attributes of the list items, such as filter to show only those who belong to EMEA or those whose status is "in progress," build pre-set user-based filters.
Use the new Users list.
Create modules dimensioned by user with line items (formatted as lists) to hold the different criteria to be used.
Create a module dimensioned by Users and the list to be filtered. In this module resolve the filter criteria from above against the list attributes to a single Boolean.
Apply this filter in the target module.
Educate the users to use the refresh button, rather than create an "Open Dashboard" button.
Color code your grid
Use colored cells to call attention to areas of a grid, such as green for positive and red for negative.
Color code cells that specifically require data entry.
Display the full details
If a large grid is required, something like 5k lines and 100 columns, then:
Make it available in a dedicated full-screen dashboard via a button available from the summary dashboard, such as an action button.
Do not add such a grid to a dashboard where KPIs, charts, or multiple grids are used for planning.
These dashboards are usually needed for ad-hoc analysis and data discovery, or random verification of changes, and can create a highly cluttered dashboard.
The main chart goes hand-in-hand with the main grid. Use it to compare one or more of the KPIs of the main grid across the different rows.
If the main grid contains hundreds or thousands of items, do not attempt to compare this in the main chart. Instead, identify the top 20 rows that really matter or that make most of the KPI value and compare these 20 rows for the selected KPI.
Location: Directly below or to the right of main display grid; should be at least partially visible with no scrolling.
Synchronization with a selection of KPI or row of the main display grid.
Should be used for:
Comparison between row values of the main display grid.
Displaying difference when the user makes change/restatement or inputs data.
In cases where a chart requires 2–3 additional modules to be created: Implement and test performance.
If no performance issues are identified, keep the chart.
If performance issues are identified, work with product owners to compromise.
These are the grids that will provide more details for an item selected on the main grid. If territories are displayed as rows, use an info grid to display as many line items as necessary for this territory. Avoid cluttering your main grid by displaying all of these line items for all territories at once. This is not necessary and will create extra clutter and scrolling issues for end users.
Location: Below or to the right of the main display grid.
Synced to selection of list item in the main display grid.
Should read vertically to display many metrics pertaining to list item selected.
Similar to info grids, an info chart is meant to compare one or more KPIs for a selected item in the rows of the main grid.
These should be used for:
Comparison of multiple KPIs for a single row.
Comparison or display of KPIs that are not present on the main grid, but are on info grid(s).
Comparing a single row's KPI(s) across time.
Place it on the right of the main grid, above or below an info grid.
Specific action buttons
Location: Below main grid; Below the KPI that the action is related to, OR to the far left/right - similar to "checkout."
Should be an action that is to be performed on the selected row of the main grid.
Can be used for navigation as a drill down to a detailed view of a selected row/list item.
Should NOT be used as lateral navigation between dashboards; Users should be trained to use the left panel for lateral navigation.
Individual line items
Serve as a call out of important KPIs or action opportunities (i.e., user setting container for explosion, Container Explosion status).
If actions taken by users require additional collaboration with other users, it should be published outside the main grid (giving particular emphasis by publishing the individual line item/s).
Call to action.
Serves as a header for a grouping.
Short sentence describing what the user should be performing within the grouping.
Formatted in "Heading 2."
Directly located next to a drop-down, input field, or button where the function is not quite clear.
No more than 5–6 words.
Formatted in "instructions."
Use Tooltips on modules and line items for more detailed instructions to avoid cluttering the dashboard.
Personal dashboards are a great new feature that enables end users to save a personalized view of a dashboard. To get the most out of this feature, here are a few tips and tricks.
Tidy Up Dashboards
Any change to a master dashboard (using the Dashboard Designer) will reset all personal views of a dashboard, so before enabling personal dashboards, take some time to ensure that the current dashboards are up to date:
Implement any pending development changes (including menu options).
Turn on the Dashboard Quick Access toolbar (if applicable).
Check and amend all text box headings and comments for size, alignment, spelling, and grammar.
Delete or disable any redundant dashboards to ensure end users don’t create personal views of obsolete dashboards.
Use Filters R ather Th an Show/Hide
It’s best practice to use a filter rather than show and hide for the rows and/or columns on a grid.
This is now more beneficial because amending the items shown or hidden on a master dashboard will reset the personal views. For example, suppose you want to display just the current quarter of a timescale. You could manually show/hide the relevant periods, but, at quarter end when the Current Period is updated, the dashboard will need to be amended, and all those personal views will be reset. If you use a filter, referencing a time module, the filter criteria will update automatically, as will the dashboard. No changes are made to the master dashboard, and all the personal views are preserved.
Create a Communication and Migration Strategy
Inevitably there will be changes that must be made to master dashboards. To minimize the disruption for end users, create a communication plan and follow a structured development program . These can include the following:
Bundle up dashboard revisions into a logical set of changes.
Publish these changes at regular intervals (e.g., on a monthly cycle).
Create a regular communication channel to inform users of changes and the implications of those changes.
Create a new dashboard, and ask end users to migrate to the new dashboard over a period of time before switching off the old dashboard.
Application Lifecycle Management (ALM)
If you are using ALM: any structural changes to master dashboards will reset all personal views of dashboards.
These dashboards are absolutely critical to good usability of a model. Dashboards are the first contact between the end users and a model.
What SHOULD NOT be done in a landing dashboard:
Display detailed instructions on how to use the model. See "Instruction Dashboard" instead.
Use it for global navigation, built using text boxes and navigation buttons.
It will create maintenance challenges if different roles have different navigation paths.
It's not helpful once users know where to go.
What SHOULD be done in a landing dashboard:
Display KPIs with a chart that highlights where they stand on these KPIs, and highlight gaps/errors/exceptions/warnings.
A summary/aggregated view of data on a grid to support the chart. The chart should be the primary element.
Short instructions on the KPIs.
A link to an instruction-based dashboard that includes guidance and video links.
A generic instruction to indicate that the user should open the left-side sliding panel to discover the different navigation paths.
Users who perform data entry need access to the same KPIs as execs are seeing.
Landing dashboard example 1:
Displays the main KPI, which the planning model allows the organization to plan.
Landing dashboard example 2:
Provides a view on how the process is progressing against the calendar.
Landing dashboard example 3:
Created for executives who need to focus on escalation. Provides context and a call to action (could be a planning dashboard, too).
Problem to solve:
As an HR manager, I need to enter the salary raise numbers for multiple regions that I'm responsible for.
As a domain best practice, my driver-based model helps me to enter raise guidelines, which will then change at the employee level.
Usability issue addressed: I have ten regions, eight departments in each, with a total of 10,000+ employees. I need to align my bottom-up plan to the down target I received earlier.
I need to quickly identify what region is above/behind target and address the variance. My driver-based raise modeling is fairly advanced, and I need to see what the business rules are. I need to quickly see how it impacts the employee level.
Call to action:
Step 1: Spot what region I need to address.
Step 2: Drill into the variances by department.
Steps 1 & 2 are analytics steps: "As an end user, I focus first on where the biggest issues are." This is a good usability practice that helps users.
Step 3: Adjusting the guidelines (drivers)
There are not excessive instructions on how to build and use guidelines, which would have cluttered the dashboard. Instead, Anaplan added a "view guideline instruction" button. This button should open a dashboard dedicated to detailed instructions or link to a video that explains how guideline works.
The chart above the grid will adjust as guidelines are edited. That is a good practice for impact analysis— no scrolling or clicking needed to view how the changes will impact the plan.
Step 4: Review a summary of the variance after changes are made. Putting steps 1–4 close to each other is a usable way of indicating to a user that he/she needs to iterate through these four steps to achieve their objective, which is to have every region and every department be within the top down target.
Step 5: A detailed impact analysis, which is placed directly below steps 3 and 4. This allows end users to drill into the employee-level details and view the granular impact of the raise guidelines.
Notice the best practices in step 5:
The customer will likely ask to see 20 to 25 employee KPIs across all employees and will be tempted to display these as one large grid. This can quickly lead to an unusable grid made of thousands of rows (employees) across 25 columns.
Instead, we have narrowed the KPI list to only ten that display without left-right scrolling.
Criteria to elect these ten: be able to have a chart that compares employees by these KPIs.
The remaining KPIs are displayed as an info grid, which only displays values for the selected employee. Things like region, zip codes, and dates are removed from the grid as they do not need to be compared side-by-side with other KPIs or between employees.
Deal with monthly dashboards
Many FP&A dashboards will need to display all 12 months in the current year, as well as Quarter, Half, and Total Year totals. Doing this is likely to create a very large grid, especially if more than one dimension is nested on the rows.
The grid displayed here is what may be requested when Anaplan is replacing a spreadsheet-based solution. The requirement being "At minimum, do what we could do in the spreadsheets".
Avoid the trap of rebuilding this in Anaplan. Usually, this simply creates an extra requirement to export this into Excel ® , have users work offline, and then import the data back into Anaplan, which kills the value that Anaplan can bring.
Instead, build the dashboard as indicated below:
Have end users view the aggregated values on the Cost center (the first nested dimensions) that will provide an overview on where most OPEX are spent
Have end users highlight a cost center, and enter its detailed sub-accounts
Visualize the monthly trend using a line chart for the selected sub-account