Forum Discussion

MJ_Holland's avatar
MJ_Holland
Regular Visitor
4 months ago
Solved

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's avatar
    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.

     

rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
4 months ago

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's avatar
    MJ_Holland
    Regular Visitor
    4 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.

    • v-veshwara-msft's avatar
      v-veshwara-msft
      Icon for Community Support rankCommunity Support
      4 months ago

      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's avatar
        MJ_Holland
        Regular Visitor
        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.