Deneb/Vega-Lite can be used to display a series of bars and labels thus creating a waterfall chart. The period-based example herein consists of 3 parts: a “begin” column, twelve period (monthly) increase or decrease ranged columns, and an “end” column. The data used is a simple sample Sales dataset (the Enterprise DNA Practice Dataset), with a disconnected years table, a couple of simple report filters to reduce the data, and three DAX measures added to ease the analysis.
This example illustrates a number of Deneb/Vega-Lite features, including:
0 - General:
- use of a “transform” block to extend the dataset with an in-visual calculations for previous month amount, amount and percent variance, and labels
- use of a “params” block to enhance the dataset with variables for the column colours
- out colour: Power BI theme colour = Divergent/Min
- positive colour: Power BI theme colour = Sentiment/Positive
- negative colour: Power BI theme colour = Sentiment/Negative
- use of an “layer” block consisting of 6 sections: 3x bar (before year, in-year, after year), 3x text (out-year, in-year positive, in-year negative)
1 - Before Period:
- use of a “transform” block to filter the dataset (month number = 0)
- use of a “bar” mark with 60% opacity and the “out” colour parameter
- use of a Y-axis with approximately 10 ticks
2 - Period:
- use of a “bar” mark with 60% opacity ranged from period minimum to period maximum
- use of conditional colour (if variance < 0 then “negative” colour parameter else “positive” colour parameter)
3 - After Period:
- use of a “transform” block to filter the dataset (month number = 13)
- use of a “bar” mark with 60% opacity and the “out” colour parameter
4 - Outside Period Labels:
- use of a “transform” block to filter the dataset (month number = 0 or 13)
- use of a “text” mark using Vega-Lite’s formatting for SI units and 4 significant digits
5 - Inside Period Labels (Positive):
- use of a “transform” block to filter the dataset (variance >= 0 and 1 <= month number <= 12)
- use of a “text” mark with line-break character to display both the signed amount variance (using Vega-Lite’s formatting for SI units and 2 significant digits) and signed percent variance (using Vega-Lite’s formatting for percent and 1 decimal place)
- Y position set to above period maximum
6 - Inside Period Labels (Negative):
- use of a “transform” block to filter the dataset (variance < 0 and 1 <= month number <= 12)
- use of a “text” mark with line-break character to display both the signed amount variance (using Vega-Lite’s formatting for SI units and 2 significant digits) and signed percent variance (using Vega-Lite’s formatting for percent and 1 decimal place)
- Y position set to below period minimum
DAX Measures:
Amount =
VAR _DisconnectedYear = SELECTEDVALUE( Years[Disconnected Year] )
VAR _Year = SELECTEDVALUE( Dates[Year] )
VAR _Month = SELECTEDVALUE( Dates[Month] )
VAR _BeforeYear =
CALCULATE( [Total Sales],
FILTER( Dates,
Dates[Year] = ( _DisconnectedYear - 1 ) && Dates[Month] = 12 ) )
VAR _InYear =
CALCULATE( [Total Sales],
FILTER( Dates,
Dates[Year] = _DisconnectedYear ) )
VAR _AfterYear =
CALCULATE( [Total Sales],
FILTER( ALL( Dates ),
Dates[Year] = _DisconnectedYear && Dates[Month] = 12 ) )
VAR _Result = SWITCH( TRUE(),
MAX( Dates[Year] ) = ( _DisconnectedYear - 1 ) && MAX( Dates[Month] ) = 12, _BeforeYear,
MAX( Dates[Year] ) = _DisconnectedYear, _InYear,
MAX( Dates[Year] ) = ( _DisconnectedYear + 1 ) && MAX( Dates[Month] ) = 1, _AfterYear,
BLANK()
)
RETURN
_ResultMonth Name =
VAR _DisconnectedYear = SELECTEDVALUE( Years[Disconnected Year] )
VAR _Year = SELECTEDVALUE( Dates[Year] )
VAR _Month = SELECTEDVALUE( Dates[Month] )
VAR _Result = SWITCH( TRUE(),
MAX( Dates[Year] ) = _DisconnectedYear - 1 && MAX( Dates[Month] ) = 12, “BEGIN”,
MAX( Dates[Year] ) = _DisconnectedYear, MAX( Dates[Month Short] ),
MAX( Dates[Year] ) = _DisconnectedYear + 1 && MAX( Dates[Month] ) = 1, “END”,
BLANK()
)
RETURN
_ResultMonth Number =
VAR _DisconnectedYear = SELECTEDVALUE( Years[Disconnected Year] )
VAR _Year = SELECTEDVALUE( Dates[Year] )
VAR _Month = SELECTEDVALUE( Dates[Month] )
VAR _Result = SWITCH( TRUE(),
MAX( Dates[Year] ) = _DisconnectedYear - 1 && MAX( Dates[Month] ) = 12, 0,
MAX( Dates[Year] ) = _DisconnectedYear, _Month,
MAX( Dates[Year] ) = _DisconnectedYear + 1 && MAX( Dates[Month] ) = 1, 13,
BLANK()
)
RETURN
_Result
The intent of this example is not to provide a finished visual, but rather to serve as a starting point for further custom visual development.
This example is provided as-is for information purposes only, and its use is solely at the discretion of the end user; no responsibility is assumed by the author.
Greg
Deneb Examples - Waterfall Chart.pbix (1.7 MB)
