2024 Week 1 | Power BI: Stacked Bridge with Core Visual

Power BI, Workout Wednesday / By

Introduction

This week’s challenge builds on last weeks challenge to utilise updated column fill and data labelling features from the Power BI December 2023 release.

The challenge will be to create a stacked bridge chart using the core column and line combo chart.

Building the bridge will require calculated tables. Intermediate users can use the sales values from the sample semantic model to build a bridge with positive values. Advanced users can try their hand at building a bridge with profit values from the sample semantic model to account for positive and negative values.

Requirements

Power BI Desktop
  1. Obtain the sample semantic model from a New Power BI file, or obtain the sample from Excel here
  2. Add the combo chart to the canvas
  3. Create a calculated table that summarises the Categories to be used along the Category axis of your Bridge chart and append a total row. Place the Categories field of the calculated table in the X-Value field well.

For example :

Waterfall Calculated Categories =
var CategoriesColumn =SUMMARIZE(‘financials’, ‘financials'[Month Name], ‘financials'[Month Number])
var TotalColumn = {(“Total”,13)}
RETURN
UNION(CategoriesColumn,TotalColumn)
  1. Create a calculated table that summarises the Series to be used for your stacked columns and append a cumulative total row. Place the series field of the calculated table in the Y-Value field well.
  2. Create a measure that calculates sales by category except the total category
  3. Create a measure that calculates the cumulative total for all categories except the total category, subtract the current category sales (2) from the cumulative
  4. Create a measure that calculates the sales by category, and for the total category calculates the grand total sum of sales for all shown categories.
  5. Create a measure that calculates the cumulative (2) for series cumulative row and sales across all categories (4) for the remaining series. Place this measure in the Y-Value field well.
  6. Create a measure that calculates the cumulative total for all categories. Place this in the line field well
  7. Set the Cumulative Total series column fill to transparent
  8. Add data labels

Dataset

The dataset this week can be obtained here: https://learn.microsoft.com/en-us/power-bi/create-reports/sample-financial-download 

Share

After you finish your workout, share on Twitter using the hashtags #WOW2024 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year

Solution

Solution File available for download via Data Stories Gallery

Scroll to Top