EBIT Financial Bridge - Dynamic Start and End

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: ...

Need Help

MJ_Holland

4 months ago

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.

MJ_Holland

Regular Visitor

5 months ago

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.