Deneb Example - Waterfall Chart

Deneb Showcase
1

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
_Result

Month 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
_Result

Month 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)

2 Likes
2

marking as solved

1 Like