EBIT Financial Bridge - Dynamic Start and End

[MJ_Holland]

Hi,

I'm looking to create an EBIT Bridge Chart in Power BI without having to use any of the custom visuals - we're not able to add them to our PBIX files. 

Something similar to this:

In my example the starting and end columns should come from measures that can be dynamically amended by users. Then all the steps will be taken from the Variance between these figures, along some kind of Dimension - which can be different between different pages.

The first column is a measure called Base Comparison. This measure interacts with a parameter slicer to make the value dynamic. Here's the measure:

Base Comparison = 
SWITCH( SELECTEDVALUE('P: Scenario Comparison'[SCENARIO_COMPARISON_KEY]), 
    1, [Base Prior Forecast],
    2, [Base Prior Year Actuals],
    3, [Base Actuals] + [Base Forecast],
    4, [Base Budget New-Y1],
    5, [Base Budget Current])

The final column is a measure called Base Metric. This measure also interacts with a parameter slicer to make the value dynamic: Here's the measure:

Base Metric = 
SWITCH ( SELECTEDVALUE('P: Scenario Metric'[SCENARIO_METRIC_KEY]),
    1, [Base Actuals] + [Base Forecast],
    2, [Base Budget New-Y1] )

The values between each of these could come from Base Variance, which is the difference between these 2. Here's the measure:

Base Variance = [Base Metric] - [Base Comparison]

These measures are all created on the same table. In the past we've had to create 3 different visuals - bar charts for the Comparison and Metric visual and a waterfall for the Variance with Accounts giving the breakdown. The issue is I can't combine these in the standard Power BI waterfall chart visual.

Does anyone have any thoughts on how I could achieve this? Perhaps another chart could be hacked to allow for this set up? Or would a calculated table need to be added to achieve this? It would be great to have this done in one visual.

[danextian]

Hi @MJ_Holland 

This can be done using the native waterfall chart. It requires a few measures and some helper tables. In the example below, three additional tables are used: one listing all measure names to act as the start and end points, and two more tables that control which measure is visible as the start and end points.

The measure below is used as the value. Of course, what I used are just dummies!

Waterfall Measure = 
VAR _value =
    SWITCH (
        SELECTEDVALUE ( WaterfallDim[Sort] ),
        1, [Total Revenue] * 1.1,
        2, [Total Revenue] * .9,
        3, [Total Revenue] * 1.15,
        4, [Total Revenue]
    )
VAR _filters =
    UNION (
        DISTINCT ( WaterfallDimStart[Start] ),
        DISTINCT ( WaterfallDimEnd[End] )
    )
RETURN
    IF ( SELECTEDVALUE ( WaterfallDim[Value] ) IN _filters, _value )

  Please see the attached pbix.

[MJ_Holland]

Hi @danextian ,

Thanks for this. I think I'd like a solution that doesn't need to have 3 separate tables to set this up. Also, with your set up the steps are the different parameter options for Comparison and Metric, there's no Variance. But I think I see I would replace the Start and End steps with measures for the Variance, similiar to the solution suggested by @rohit1991 .

[rohit1991]

Hii @MJ_Holland 

This can be achieved using the standard Power BI Waterfall visual without any custom visuals. The key is to create a disconnected Bridge Steps table that defines the order of the columns (Start >> Variance components >> End) and a single measure that dynamically returns values for each step.

Use your existing measures for the first and last columns:

• Start = [Base Comparison]
• End = [Base Metric]
• Intermediate steps = [Base Variance] calculated by account/category using CALCULATE.

Example DAX:

Bridge Steps =
DATATABLE(
    "Step", STRING, "Sort", INTEGER,
    {
        {"Start", 1},
        {"Variance", 2},
        {"End", 3}
    }
)

Bridge Value :=
VAR Step = SELECTEDVALUE('Bridge Steps'[Step])
RETURN
SWITCH(
    Step,
    "Start", [Base Comparison],
    "End", [Base Metric],
    [Base Variance]
)

Place Step on the Category and Bridge Value on the Y-axis of the Waterfall chart, then set the Start and End columns as Totals. This enables a fully dynamic EBIT bridge in a single visual.

[MJ_Holland]

Hi @rohit1991 ,

We're almost there with this one - the start and end values are correct but I would need to split out variance. I've amended your table set up to the following:

CT: EBIT Bridge Steps = 
DATATABLE (
    "Step Order", INTEGER,
    "Step Name", STRING,
    {
        { 1, "Comparison" },
        { 2, "Total Revenue" },
        { 3, "Total Expenses" },
        { 4, "Depreciation & Amortisation" },
        { 5, "Metric" }
    }
)

Then I'm amending the DAX Measure to have specific values for each of the Variance Steps:

EBIT Bridge Value = 
SWITCH(
    SELECTEDVALUE('CT: EBIT Bridge Steps'[Step Name]),
    "Comparison", [Base Comparison],
    "Total Revenue", CALCULATE([Base Variance], 'D: Account'[LEVEL_10_ACCT_CODE] = "REV000"),
    "Total Expenses", CALCULATE([Base Variance], 'D: Account'[LEVEL_10_ACCT_CODE] = "EXP000"),
    "Depreciation & Amortisation", CALCULATE([Base Variance], NOT ('D: Account'[LEVEL_10_ACCT_CODE] IN {"REV000", "EXP000"})),
    "Metric", [Base Metric]
)

I just need to check if the end value is actually showing correctly as I'm not sure if the Total Column is showing the value I need.

[v-veshwara-msft]

Hi @MJ_Holland ,

Just checking in on this. Hope you had a chance to review the final value.

Please let us know if it is aligning as expected or if you are still seeing any differences. We can review further based on your findings.

[MJ_Holland · Accepted solution]

Hi @v-veshwara-msft ,

I've taken some of the above and come up with the following solution that works for what I need:

I start by creating a table that contains a list of the dynamic start steps from my Base Comparison I need along with the various bridge steps from my Base Variance. I exclude the final dynamic step as the Total column will automatically calculated the Base Metric value I need between the Comparison and Variance:

CT: EBIT Bridge = 
VAR BaseCompList = VALUES ( 'P_Scenario Comparison'[Scenario Comparison] )
RETURN
GENERATE (
    BaseCompList,
    DATATABLE (
        "Step Order", INTEGER,
        "Step Name", STRING,
        {
            { 1, "Base Comparison" },
            { 2, "Total Revenue" },
            { 3, "Total Expenses" },
            { 4, "Depreciation & Amortisation" }
        }
    )
)

Then I create a calculated column that will be used in the Category well of the Waterfall chart and will show the dynamic label for the first step:

Step Label = 
IF (
    'CT: EBIT Bridge'[Step Order] = 1,
    'CT: EBIT Bridge'[Scenario Comparison],
    'CT: EBIT Bridge'[Step Name]
)

After that, I create the measure for my Y-Axis:

EBIT Bridge Values = 
VAR StepNo = SELECTEDVALUE ( 'CT: EBIT Bridge'[Step Order] )
VAR SelectedScenario = SELECTEDVALUE ( 'P_Scenario Comparison'[Scenario Comparison] )
VAR RowScenario = SELECTEDVALUE ( 'CT: EBIT Bridge'[Scenario Comparison] )
RETURN
SWITCH (
    TRUE (),
    // Step 1: only show value when the row scenario matches the slicer selection
    StepNo = 1 && RowScenario = SelectedScenario, [Financial Base Comparison],
    StepNo = 1 && RowScenario <> SelectedScenario, BLANK(),
    // Other steps (your existing logic)
    StepNo = 2, CALCULATE ( [Financial Base Variance], D_Account[Level 10 Account Group] = "Total Revenue" ),
    StepNo = 3, CALCULATE ( [Financial Base Variance], D_Account[Level 10 Account Group] = "Total Expenses" ),
    StepNo = 4, CALCULATE ( [Financial Base Variance], D_Account[Level 10 Account Group] = "Depreciation & Amortisation" ),
    BLANK()
)

This seems to do the trick. I may need to double check the value in my starting value as it may be double counting, but I need to check on that.

The final piece would be to have a dynamic Mix and Max axis. But that's for another thread.

[v-veshwara-msft]

Hi @MJ_Holland ,

Thank you for taking time and sharing what worked for you. This will help others who are following this thread in finding solution quickly. 

Please continue using Fabric Community for further queries.

Best Regards,
Community Support Team.