On Handling Precalculated Hierarchical Data in Power BI

Author:Murphy | View: 29610 | Time: 2025-03-22 21:54:41

While hierarchies are a familiar concept with data, some sources deliver their data in an unusual format. Usually, we get our values at the lowest level. But what happens when we get pre-aggregated values? Here, I will dive into this topic.

Introduction and the Data

Let's set the scenario: We have an organization with administrative expenses.

The expenses can happen at the country, state, and store level.

Look at the following table:

Figure 1 – Data with the value in the expected place (Figure by the Author)

We see two rows for expenses for the two Stores and one for the organizational expenses for the State of South Carolina.

I can use this data to calculate the sum of the expenses and get the total expenses for all stores in South Carolina.

But what when the source system delivers the data in a different form?

For example, like this:

Figure 2 – Data with pre-aggregated values for South Carolina (Figure by the Author)

The third row contains the pre-aggregated sum for the two stores in South Carolina plus the organizational expenses for the State of South Carolina.

A simple sum of these three rows would return a wrong result, as the result would contain the expenses for the two stores twice:

Figure 3 – Wrong result when aggregating the data with the pre-aggregated values (Figure by the Author)

The challenge is: How can I calculate the correct result for each level in the hierarchy?

My approach to the solution must consider the following:

I cannot change the data at the source.
I must add some calculations in the data model to correct the result.
I must perform different calculations at each level of the hierarchy.

But where and how can I do it?

I have three possibilities to solve this issue:

Add a calculated column to get the correct result.
Add a Measure that calculates the correct result.
Use Visual calculations.

Calculated columns

OK, let's start adding some calculated columns.

First, I need to know at which level each row is within the hierarchy. For this, I need a column named "Path Length". Such a column is usually used when handling Parent-Child hierarchies.

Therefore, I add two new columns to be able to navigate the hierarchy better:

Figure 4 – Additional calculated column for hierarchy navigation (Figure by the Author)

I used the following expression to calculate the HierachyPath column:

HierarchyPath =
'Cost Data'[Country]
    & IF (
        'Cost Data'[State] <> 'Cost Data'[Country],
        "|" & 'Cost Data'[Country]
    )
    & IF (
        'Cost Data'[Store] <> 'Cost Data'[State],
        "|" & 'Cost Data'[Store]
    )

Then, I used the PATHLENGHTH() function to calculate the "Path Length" column:

Path Length = PATHLENGTH('Cost Data'[HierarchyPath])

Next, I can write an expression to perform the following steps for each row in the table:

Get the value for the current position.
Get the sum of values below the current position in the hierarchy.
Detract the sum from step 2 from the value in the current row.

The result is a column containing the values in the first picture above.

Corrected Expenses = 
VAR CurrentExp = 'Cost Data'[Expenses]
VAR CurrentLevel = 'Cost Data'[Path Length]
VAR CurrentPath = 'Cost Data'[HierarchyPath]

VAR ChildExpenses = 
            CALCULATE(SUM('Cost Data'[Expenses])
                        ,REMOVEFILTERS('Cost Data')
                        ,'Cost Data'[Path Length] = CurrentLevel + 1
                        ,CONTAINSSTRING('Cost Data'[HierarchyPath], CurrentPath)
            )

RETURN
    CurrentExp - ChildExpenses

The key here is the expression for the variable "ChildExpenses". This expression calculates the sum of all rows below the current position but with the same parent.

Be aware that calling CALCULATE() for a calculated column in Power BI triggers a Context Transition.

If you are not familiar with the concept of Context Transition, make sure to read the article where I explain it:

What's fancy about context transition in DAX

This is the result for the column:

Figure 5 – Result of calculated column to get the correct result (Figure by the Author)

This column replaces the original Expenses column.

I will rename the original Expenses column "Expense_Original" and the calculated column "Expenses". The column Expense_Original is hidden in the Data model, as it is useless for the report.

Now, I can create the report intuitively:

Figure 6 -The renamed Original Expenses and the calculated column side-by-side in Power BI (Figure by the Author)

This is the needed result.

But let's see if I can create a measure to calculate the correct result.

Measures

To write a Measure, I must handle each hierarchy level separately.

I cannot use the same method as with the calculated column, as there are multiple rows at the Store level for each level above (Country or State).

The result is the following Dax Code:

Expenses (Corrected) = 
VAR CurrentExp = [Expenses (Original)]
VAR CurrentLevel = SELECTEDVALUE('Cost Data'[Path Length])
VAR CurrentPath = SELECTEDVALUE('Cost Data'[HierarchyPath])

VAR CurrentCountry = SELECTEDVALUE('Cost Data'[Country])
VAR CurrentState = SELECTEDVALUE('Cost Data'[State])
VAR CurrentStore = SELECTEDVALUE('Cost Data'[Store])

VAR StateExpenses =
             -- Get the pre-aggregated value of the Expenses for the State
            CALCULATE([Expenses (Original)]
                        ,REMOVEFILTERS('Cost Data')
                        ,'Cost Data'[Path Length] = CurrentLevel + 1
                        ,CONTAINSSTRING('Cost Data'[HierarchyPath], CurrentPath)
            )

RETURN
    SWITCH(TRUE()
        -- Calculation at the lowest level (Store)
        -- But only when the Store has a different name than the State
        ,NOT ISBLANK(CurrentStore) && CurrentStore <> CurrentState
            ,CurrentExp
        -- Detract the Expenses from the sum at the State level when the "Store" has the same name as the State
        -- These are the rows with the Expenses for the State
        ,NOT ISBLANK(CurrentStore) && CurrentStore = CurrentState
            ,CurrentExp - StateExpenses
        -- Calculate the Sum at the state level
        ,NOT ISBLANK(CurrentState) && ISBLANK(CurrentStore)
            -- First, calculate the Sum for all Stores
            -- But only when the Stores have a different name than the State
            ,CALCULATE([Expenses (Original)]
                        ,REMOVEFILTERS('Cost Data')
                        ,'Cost Data'[Country] = CurrentCountry
                        ,'Cost Data'[State] = CurrentState
                        ,'Cost Data'[Store] <> CurrentState
                        )
                    -- At this stage, each row in the Visual has multiple Data rows.
                    -- Therefore, SELECTEDVALUE() for the path doesn't return any value.

                    -- Now add the sum for all Stores, detracting the duplicate value for the "Stores" with the same name as the State
                        +
                        (
                        CALCULATE([Expenses (Original)]
                                    ,REMOVEFILTERS('Cost Data')
                                    ,'Cost Data'[Country] = CurrentCountry
                                    ,'Cost Data'[State] = CurrentState
                                    ,'Cost Data'[Store] = CurrentState
                                )
                        -
                        CALCULATE([Expenses (Original)]
                                    ,REMOVEFILTERS('Cost Data')
                                    ,'Cost Data'[Country] = CurrentCountry
                                    ,'Cost Data'[State] = CurrentState
                                    ,'Cost Data'[Store] <> CurrentState
                                    )
                        )
            -- Calculate the corrected Sum for the Country
            -- Must use the same logic as above, but by moving one level above, considering only the Country and the State
            ,CALCULATE([Expenses (Original)]
                        ,REMOVEFILTERS('Cost Data')
                        ,'Cost Data'[Country] = CurrentCountry
                        ,'Cost Data'[State] <> CurrentCountry
                        ) + 
                        (
                        CALCULATE([Expenses (Original)]
                                    ,REMOVEFILTERS('Cost Data')
                                    ,'Cost Data'[Country] = CurrentCountry
                                    ,'Cost Data'[State] = CurrentCountry
                                )
                        -
                        CALCULATE([Expenses (Original)]
                                    ,REMOVEFILTERS('Cost Data')
                                    ,'Cost Data'[Country] = CurrentCountry
                                    ,'Cost Data'[State] <> CurrentCountry
                                    )
                        )
    )

I have added extensive comments within the code.

Therefore, I refrain from explaining each step of the Measure.

However, this approach is very complicated and is no match to the simplicity of the approach with the calculated column.

Visual calculations

Lastly, I can use one of the latest features in Power Bi: Visual Calculations.

Visual calculations can be used to add calculations directly to a Visual without adding a Measure to the Data model.

This opens up some exciting possibilities and removes the need for Measures written to fulfill the requirements of one specific visual.

I have added some links to this topic in the References section below.

Here, I tried using the new feature to implement a simple solution.

However, I couldn't find a working solution after much research and trial and error.

I found a solution to calculate the correct result for each Store but not for the States and the Countries:

Visual calculation =
VAR CurrentCountry = [Country]
VAR CurrentState = [State]

RETURN
  SWITCH(TRUE()
        ,[State] <> [Store] && ISATLEVEL([Store])
          ,[Expenses (Original)]
        ,[State] = [Store] && ISATLEVEL([Store])
          ,[Expenses (Original)] -
                CALCULATE(SUM([Expenses (Original)])
                          ,[State] <> [Store]
                          ,[Country] = CurrentCountry
                          ,[State] = CurrentState
                          )
          )

This is the result of this formula:

Figure 7 – Result of the Visual calculation (Figure by the Author)

I tried to find a solution to calculate the State and Country results. But I wasn't successful.

One crucial detail is that we don't get a grand total with this approach. It might be an artifact of the incomplete formula. But it's an important detail.

Regardless of the lack of success with this specific scenario, I would include this new feature in my skill set. And I encourage you to look at this new, exciting feature. It offers new possibilities for finding solutions to calculate results specific to one Visual that will not be reused otherwise.

Amit Chandak has written an introduction to this topic:

Understanding Visual Calculations in Power BI: Revolutionizing Data Analysis

Conclusion

When I started writing this piece, I intended to provide you with three working solutions for the problem.

I found two working solutions. The first is the most straightforward and efficient, while the second is a good exercise in working with hierarchies.

Some time ago, I have written a short piece about why pre-aggregated data is bad for us:

Pre-calculated aggregations for Power BI – Why should you avoid them

Now, I have one more example of why I was right when I wrote that.

One important takeaway is that preparing and formatting the data to support easy solutions is essential, even when this means going the extra mile to find the solution.

References

Here are some references about the new Visual calculations feature:

Visual calculations (preview) on the MS Power Blog (February 2024)

Using visual calculations (preview)

SQLBI Articles page (Contains several articles on this topic with more to come)

I use the Contoso sample dataset, as I did in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described here.

I extracted a subset of the data and manipulated it to get the needed data.

Please consider Following me and Subscribe to get E-Mails as soon as I add new content:

Get an email whenever Salvatore Cagliari publishes.

I make my articles accessible to everyone, even though Medium has a paywall. This allows me to earn a little for each reader, but I turn it off so you can read my pieces without cost.

You can support my work, which I do during my free time, through

https://buymeacoffee.com/salvatorecagliari

Or scan this QR Code: