Tracking Project Benefit in Power BI – Part 2

Welcome back to the second part of our series on Tracking Project Benefit in Power BI! In the first part, we laid the foundation by structuring our data model and establishing the necessary relationships. We discussed the importance of this visual and broke down the data model.

If you missed the first part, you can catch up here.

In this part, we’ll dive into the DAX calculations required to bring our chart to life. We’ll walk through each step, ensuring that our calculations are accurate and our visualisation effectively communicates the data. Let’s get started!

Calculations and DAX

For this burnup chart, we’ll use cumulative totals. Let’s dive into calculating the Total Benefit Baseline. First, we’ll calculate the cumulative total of the Baseline Financial Benefit.

_Cumulative_Benefit_Baseline = 
CALCULATE (
    SUM('Facts_Programme Benefits'[Cost Baseline]),
    FILTER (
        ALL ( Dim_Date ),
        Dim_Date[Date] <= MAX ( Dim_Date[Date] )
    )
)

Here’s a breakdown of the DAX:

1. SUM(‘Facts_Programme Benefits'[Cost Baseline]): This part calculates the total sum of the Cost Expected column from the Facts_Programme Benefits table.
2. CALCULATE(…, FILTER(…)): CALCULATE is used to modify the context in which calculations are performed. Here, it is used to ensure that the summation of Cost Expected is calculated within a specific context defined by the FILTER function.
3. FILTER(ALL(Dim_Date), Dim_Date[Date] <= MAX(Dim_Date[Date])):
   • FILTER is used to create a new table that only includes rows meeting certain criteria.
   • ALL(Dim_Date) removes any filters that might have been applied to the Dim_Date table, allowing the formula to consider all dates.
   • Dim_Date[Date] <= MAX(Dim_Date[Date]) means the formula will filter rows where the date is less than or equal to the maximum date in the current context. This creates a cumulative effect because it includes all dates up to the current date.

Then we need to calculate the Cumulative total of Benefit Realised. For that I will split the calculation in two. First let’s look into the calculation of the total.

For this I want to use the values from the column [Cost Realised] if the filed is not blank and if the field is blank, it means the benefit has not been realised yet, so we need to use the values from [Cost Baseline]. Here is the DAX:

_Benefit_Outturn = 
SUMX (
    'Facts_Programme Benefits',
    IF (
        ISBLANK ( 'Facts_Programme Benefits'[Cost Realised] ),
        'Facts_Programme Benefits'[Cost Baseline],
        'Facts_Programme Benefits'[Cost Realised]
    )
)

And here is the breakdown of the DAX:

1. SUMX(‘Facts_Programme Benefits’, …): SUMX is an iterator function that goes row by row through the Facts_Programme Benefits table and performs a calculation on each row.
2. IF(ISBLANK(‘Facts_Programme Benefits'[Cost Realised]), ‘Facts_Programme Benefits'[Cost Baseline], ‘Facts_Programme Benefits'[Cost Realised]): For each row in the Facts_Programme Benefits table, the IF function checks if Cost Realised is blank (i.e., has no value).
   • If Cost Realised is blank, it uses the value from Cost Baseline.
   • If Cost Realised is not blank, it uses the value from Cost Realised.

After we have this measure calculated, we can cumulative total:

_Cumulative_Benefit_Outturn = 
CALCULATE (
    [_Benefit_Outturn],
    FILTER (
        ALL ( Dim_Date ),
        Dim_Date[Date] <= MAX ( Dim_Date[Date] )
    )
)

Where:

1. [_Benefit_Outturn]: This refers to a previously defined measure called [_Benefit_Outturn]. This measure calculates the total cost using a specific logic you have defined earlier.
2. CALCULATE(…, FILTER(…)): CALCULATE is used to change the context in which calculations are performed. It modifies the existing measure by applying additional filters or conditions.
3. FILTER(ALL(Dim_Date), Dim_Date[Date] <= MAX(Dim_Date[Date])):
   • FILTER creates a new table based on certain criteria.
   • ALL(Dim_Date) removes any existing filters on the Dim_Date table, so it can consider all dates.
   • Dim_Date[Date] <= MAX(Dim_Date[Date]) sets the criteria to include only the dates that are less than or equal to the maximum date in the current context. This essentially means “all dates up to the current date.”

Now it’s time to create a measure for the error bar but first, let’s understand what they are and how I will repurpose it to display the gap between expected and realised benefits.

Error bars in Power BI are visual elements that can be added to charts to display the variability or uncertainty in the data. They are typically used to show the range of possible values, based on statistical calculations. Specifically, the error bar upper bound represents the maximum value within this range.

For this specific visual, I will use the error bar upper as a way to illustrate the benefit gap between Baseline and Outturn.

To repurpose it we first need to calculate the actual difference between the Cumulative Baseline and the Cumulative Outturn, let’s use this measure:

_Variance = [_Cumulative_Benefit_Baseline] - [_Cumulative_Benefit_Outturn]

Because this value should be above the bars with the Realised Value, we now need to add these two measures together:

_ErrorBars = [_Cumulative_Benefit_Outturn] + [_Variance]

The _Variance was calculated in a separate measure because it will be used under tooltips as Benefit Loss.

We’ve now walked through the essential DAX calculations needed to create our Financial Benefit Burn-Up Chart in Power BI. By understanding and implementing these calculations, we’re equipped to visualise the realised and expected benefits effectively.

Looking forward, the third and final part of this series will be a video on my YouTube channel @AccessibleBI. In this video, I’ll show you how to build the chart step-by-step and configure it to ensure accessibility. If you haven’t yet subscribed, make sure to do so to stay updated!

Thank you for joining me on this journey. Until next time, let’s keep crafting accessible insights that make a difference!