Introduction
In the world of financial storytelling, Waterfall charts are all time favorites. Yet, there are stories they cannot tell. When that occurs, one of the options we have is to turn to an underutilized chart within Power Bi arsenal: the Decomposition Tree.
The decomposition Tree visual in Power Bi is a powerful tool for data exploration and analysis. It allows users to break down a metric into its parts, exploring data hierarchies and uncovering patterns that might otherwise remain hidden. It’s an analytical journey, providing a path to discovery that is intuitive and insightful.
.
Advantages of the Decomposition Tree
1. Intuitive User Experience:
The Decomposition Tree’s interface is designed with the end-user in mind, ensuring that even those with minimal data analysis experience can navigate and interact with the visual effectively.
2. Transparency in Data Breakdown:
The visual displays how aggregate values are broken down into their components, making the understanding of complex datasets simple.
3. Customization and Flexibility:
With its easy-to-use customization options, users can tailor the visual to meet specific analytic needs, making it as detailed or as streamlined as required.
4. Engagement and User Adoption:
Its engaging nature not only draws users in but also facilitates a deeper interaction with the data, which is a significant factor in driving the adoption of analytics tools across an organization.
Disadvantages of the Decomposition Tree
1. Space Requirements:
The Decomposition Tree can be quite spacious, requiring a significant portion of the report page to display effectively, which could be a challenge.
2. Niche Application:
Traditionally, its use has been confined to specific scenarios where detailed data decomposition is necessary, which might not be every day for all users, potentially leading to underutilization.
3. Perceived Limitation:
By its very name, the Decomposition Tree suggests a singular function—breaking down data, which might cause users to overlook its potential for broader analytical applications.
Expectations
The limitations of traditional decomposition trees in Power Bi sparked my curiosity to explore methods that amplifies their utility with advanced DAX calculations. Imagine leveraging decomposition trees not just to break down metrics by categories but also for displaying variancies between scenarios – such as Actuals vs Planned or Forecast vs Last Year. What if we could add to this concept the possibility to integrate time periods, enabling a comparison of Actuals, Planned and Forecast 1 and 2 data across different years? This innovation would allow for an unprecedented visualization of 255 DISTINCT SCENARIOS COMBINATIONS within one single graph!
Has this piqued your interest? If so, let’s roll up our sleeves and let’s delve into the methodology behind this enhanced analytical capability.
The Role of DAX:
Alright, let’s see how this magic happens.
The initial step involves strategizing our implementation plan and breaking down the challenge into smaller manageable pieces. Before we delve deep into DAX, it is important to ensure our model is structured to support our desired objectives. Specifically, we anticipate creating a calculated table that will serve as the foundation for the button we’re about to develop.
Tips:
By using empty characters you can control the order of display in the filter. Note that the white characters are not being displayed in the filter chart.
KPI 1- 2 Measure (the measure we use inside the Analyze section in the decomposition tree graph under the name of “Total”)
The measure KPI 1-2 calculates the difference between two KPIs (KPI 1 Total Cost and KPI 2 Total Cost) under specific conditions. The logic involves several steps:
1. _CountScenario: Checks if more than one scenario is selected. This is a condition to determine how the final result will be computed.
2. _SecondKPI and _FirstKPI: Check if KPI 2 Total Cost and KPI 1 Total Cost are blank (no value). These conditions affect the final result calculation.
3. _Result: It calculates the difference between KPI 1 Total Cost and KPI 2 Total Cost if both KPIs are not blank and if more than one scenario is selected. Otherwise, it sets the result to 0.
This measure is designed to provide a comparative insight between two costs, under the condition that both are calculated (not blank) and multiple scenarios are considered
KPI 1 Total Cost Measure
This measure computes the total cost for KPI 1 based on a selected scenario and year, with additional logic for currency conversion and entity count.
1. _SelectedScenario and _SelectedYear: Extract the scenario and year selected for the calculation.
2. _CountEntity: Counts the number of unique entities selected.
3. _Result: Calculates the sum of total costs for the selected scenario and year, filtered by “MTD” view.
4. _ResultEURO: Similar to _Result, but specifically filters for costs in EUR currency.
5. The final result switches between _Result and _ResultEURO based on the entity count. If more than one entity is selected, it uses _ResultEURO.
This measure adapts to the user’s selections (scenario, year, entity) and provides a flexible cost calculation that can adjust for currency.
KPI FINAL Measure
Determines the logic for selecting the scenario based on multiple selections and year context.
1. _Multiselected: Counts the number of scenarios selected.
2. _SelectedMeasure: Captures the selected scenario.
3. _Logic: Determines the appropriate KPI value based on the number of scenarios selected and whether a multi-year selection is made.
This measure helps in scenario analysis by dynamically adjusting the scenario selection based on user input, facilitating comparisons across different conditions.
First Year Selected and Second Year Selected Measure
These measures extract the first and second years from the selected scenarios, with logic to handle multiple selections and ensure valid year values are returned.
• First Year Selected: Determines the first selected year, with logic to handle when exactly two years are selected.
• Second Year Selected: Determines the second selected year or returns blank if not applicable.
These measures are crucial for time-based analysis, allowing for comparisons across different periods based on user selections.
Format KPI 1-2 Measure
This DAX measure is designed for use in the decomposition tree visual, providing dynamic formatting based on currency and the magnitude of the category value being analyzed.
Let’s break down the components of the measure and their purposes:
Variables Defined
_Currency: Determines the currency format to use. If more than one entity is selected, it defaults to “EUR”. Otherwise, it uses the currency selected in the ‘Total Costs’ table. This allows for dynamic currency display based on the data context or user selection.
_MaxCategory: Obtains the maximum category value from a different measure, [MAXX Category Value]. This value is used to determine the formatting scale (e.g., thousands, millions).
_CountEntity: Counts the number of unique entities selected. This is used to adjust currency display based on entity selection.
Formatting Logic
The core of this measure is a nested IF statement combination that dynamically sets the number format based on the currency and the magnitude of _MaxCategory:
This DAX measure, MAXX Category Value, calculates the maximum value of the [KPI 1-2] measure across different categories, with adjustments based on the number of entities selected and the currency
• For Euro (EUR) Currency:
• The format changes depending on the value range of _MaxCategory, offering different formats for ranges below -1 million, between -1 million and -1 thousand, below 1 thousand, and so forth, up to less than 1 billion. Formats are specified for both positive and negative ranges, using “M” for millions and “K” for thousands, with appropriate formatting for negative values.
• For Local Currency (LC) When a Single Entity is Selected:
• Similar logic to the EUR formatting, but prefixed with “LC” to denote local currency, adjusting the formatting based on the value range of _MaxCategory. This allows for context-sensitive display adjustments based on the financial magnitude being analyzed.
• Default Case:
If none of the conditions are met, it defaults to a basic format ” ,0″
Conclusion
In this blog post, I delve into the versality of Decomposition Tree visualization within Power Bi and showcase how DAX amplifies its use. DAX measures are crucial for crafting sophisticated and visually appealing graphs.
Through DAX I showcased how we can ensure a bespoke user experience that allows for seamless scenario and year comparisons. This includes managing situations when dealing with selections across multiple countries with varying currencies, aiming to present data in the local currency.
DAX can facilitate dynamic conditional formatting of visualization elements, can emphasizes critical alerts, and even enriches report narratives with dynamically generated titles, subtitles and filter summaries at the botton of the page.
My goal in this blogpost is to demostrate how DAX refinements not only augment the utility of Decomposition trees but also unlock a higher lever of analytical depth in Power Bi reporting.